Over 1 million tech questions and answers.

Solved: Excel and VB: Changing cell value to text with macro

Q: Solved: Excel and VB: Changing cell value to text with macro

I have the following data:

1/1/05
2/1/05
3/1/05
Each of the cells is formatted as a date, and is a value.
What I would like to do (and what could be done under Lotus) is to record a macro that puts the ' in front of 1/1/05 and makes it a value. I realize there are other ways to do this, but the functionality of adding a character before a value is what I am looking for.

Functionally, I record this macro with these keystrokes:

F2 Home ' Enter

which results in this code:

ActiveCell.FormulaR1C1 = "'1/1/2005"
Range("A3").Select

If I run this macro, it changes the cells underneath to 1/1/05, instead of keeping their date. Where am I going wrong here and how can I make this work?

Thanks!

RELEVANCY SCORE 200
Preferred Solution: Solved: Excel and VB: Changing cell value to text with macro

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Excel and VB: Changing cell value to text with macro

Read other 16 answers
RELEVANCY SCORE 104.4

I am trying to create a macro that will select the text contents of a cell andcopy it into another cell. I know recording a macro isn't the best way to go,but I'm still pretty fresh in coding VBA.

When I record a macro on the following set of data (shown below) I cancreate a row above the first row containing "CASH" and copy the word"CASH" into the cell above it.

(sample data)

CASH 83 521.01
TRES.CARD 11 54.11
CREDIT2 0 0
HELP 0 0
SUBTOTAL 0 0

(recorded macro)

ActiveCell.Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "CASH"
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste

If I run the macro on the row containing "Tres.Card" it willreplace “TRES.CARD” with "CASH".

Essentially I am trying the make two rows for each of the existing rows. Iwould add "#" to the label in column 1 on the first line and add"$" to that label on the second line. The first line would thencontain the data from column B and the second line would contain the data fromcolumn C. So that the end result would look like:

(Example of end result)

CASH # 83
CASH $ 521.01
TRES.CARD # 11
TRES.CARD $ 54.11
CREDIT2 # 0
CREDIT2 $ 0
HELP # 0
HELP $ 0
SUBTOTAL # 0
SUBTOTAL $ 0

If anyone has any suggestions of the best way to do this that I'm not thinkingabout or aware of, I would appreciate it.

Otherwise, does ... Read more

A:Solved: Creating an Excel macro to select text content of a cell and copy it into ano

Read other 9 answers
RELEVANCY SCORE 97.6

I am trying to use part of a macro to change the active cell to a cell address determined from data entered by the user. For example, a user enters a date, the date is compared to a list of dates and when a match is found the cell address is returned. I want to then make the returned address the active cell. Can this be done and if so how?
 

A:MS Excel - Changing the Active Cell Macro

You would need to use VB code. I don't think you can record a macro to do that...do you already have a way to find the matching date or do you need help on that part too?
 

Read other 3 answers
RELEVANCY SCORE 93.2

Hi,

My VBA/Macro knowledge is very basic, but I am able to record macros and edit them. I have a scatter plot (with 5 series) and I want to automatically change the line style (i.e. solid or dashed etc.) depending on the text in a cell. This is what I tried, but I cannot get the code to reference the cell which contains the text:

--------------------------------------------------------------

Sub Macro14()
'
' Macro14 Macro
'
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
End With
End Sub
--------------------------------------------------------------

For the line ".DashStyle = msoLineSolid" I tried ".DashStyle = Range("A7").Select" but this does not work.

Is there a simpler way to set-up a macro to format graphs automatically based on a cell content/format/colour, does anyone know a good guide to do this?

Thanks
 

A:Excel Macro to automatically change chart line style based on cell text

Hi,

I'm not familiar with charts. But, see if this helps.

http://social.msdn.microsoft.com/Forums/sk/isvvba/thread/50180062-37e0-435e-bf52-0f6de482d8ef
 

Read other 3 answers
RELEVANCY SCORE 90

I can manage to create a macro to change the text of a cell to something else, but I was wondering if there is any way to add the same text to the beginning of every cell you run the macro in.

For Example I want to add the initials LSB to the beginning of several (long list) cells. Is this possible, and can anyone tell me how?
 

A:Solved: Macro to add text to a cell

Read other 10 answers
RELEVANCY SCORE 86

Hi guys,

I've attached the dummy file. You see the report groups all the relevant data pertaining to the person's name and puts it under the name of the person. I need a macro that will copy the name of the person in column A across all the rows of data pertaining to the person's name in column Z. I will not know which row the name of the person will be in or how many rows of data each person will have, so the macro needs to search through all the rows to find specific names.You'll see an example of what I need in column AA.

I then wrote a macro just based on reading in the net to remove the blank rows in column A. For example A6 is blank, so the macro I wrote deletes that column. The macro also deletes the "Total No. of Cases" row.

This is the macro:

Sub Deletejunk()
'
' Deletejunk Macro
' To delete rows containing *No. of Cases* & Blanks
'
' Keyboard Shortcut: Ctrl+Shift+O
'

With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "*No. of Cases*", xlOr, ""
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub
I would like that after the previous macro copies the names to column Z this macro could run straight after that. Even better if this macro could delete those names in column A. I'm thinking we would then have to make an array of things to... Read more

A:Solved: Macro to copy text from a cell to a column with specific criteria

Read other 8 answers
RELEVANCY SCORE 85.2

(I am unsure if the first time i posted this worked or not, but here we go again.)

I need to remove some data from cells, preserve numerical values, and add them together.

The record format is this EntityA(#records). In most cells, There is not only an Entity A, but B-H as well. I am trying to strip out the entity name information, retain the numerical value in the parentheses, and then add them together cell by cell, row by row.

I have attached a sample of the sheet.

Im not looking for anything too fancy, I've just got about 1000 rows of these and need a total count of (#records) that have been received. So in the sample, it doesn't matter that A3 = 32 total, I'm just in need of a folmula, filter, or macro that will tell me that A3-A9= 41 total. (If the "n/a" or "~" values are problematic they can just be blank.)

Can anyone help with this?
 

A:Solved: Excel Cell Macro/Filter

Read other 10 answers
RELEVANCY SCORE 84.4

Hello Macro Geniuses,

I am super new to VBA and I am sure this is a piece of cake. I need to create a button that copies that contents of say cell C3 and copies it to C4, if C4 is taken, I need it to find the next available cell.

Also, I have read in some of the other threads that access might be better but I don't have it so I would need it to be in excel.

Thanks in advance.
 

A:Solved: Excel Macro to Paste to blank cell

Read other 16 answers
RELEVANCY SCORE 84.4

I have been reading up on creating log files with Excel Macros.

I am trying to figure out if it is possible to have a Macro output the contents of the currently selected Cell to a text file?
 

A:Solved: Excel Macro Copy Cell to log file.

Read other 16 answers
RELEVANCY SCORE 84.4

I'm attempting to write my first macro for an Excel 2003 workbook. I'm not completely code illiterate (I've got moderate skills with AutoLISP), but I'm new to VBA and am not yet an Excel power user, so please be gentle.

The macro I want to write will:
check that the selected cell's content is underlined before proceeding
copy the content of the currently selected cell into an external plain text .log file
.log file lines should be: year/month/day - time - username - cell contents
.log file names will probably need to be generated
clear the cell's content and formatting (particularly underline and text/background color)
Here's what I have so far:
Code:
Sub Unpost()
If Selection.Font.Underline = True
Then Selection.ClearFormats And Selection.Clearcontents
Else
If MsgBox("The selected cell is not underlined...are you sure?", vbOkCancel) = vbOk
Then Selection.ClearFormats And Selection.Clearcontents
Else Exit Sub
End If
End If
End Sub
If I've written it correctly, it should currently do everything except log the cell contents. This, from what I've seen, is going to be the trickier part. I intend to use this macro 50+ times per weekday, so at some point the .log files will get too long to be useful, so I assume it will need to automatically create new logs (perhaps "year-month.log"). I've seen some useful info about appending to an external log here and here, ... Read more

A:Excel 2003 macro: log contents of selected cell, clear cell

You need to use the "File Scripting Object" to create and/or append text to a file. I've included a link below to get you started. If you are unable to figure it out on your own let me know and I'll write the code for you.

http://www.virtualsplat.com/tips/visual-basic-fso.asp

Rollin
 

Read other 1 answers
RELEVANCY SCORE 83.6

Is there a way to save a workbook if a cell within a range is changed?
 

A:Solved: Excel Macro -> Save Workbook On Cell Change

Read other 16 answers
RELEVANCY SCORE 83.6

Excel Marco Dropdown Menu Cell Contents I have an excel workbook with a "INPUT" WkSht, and a "ACTIVE" WkSht.
INPUT has a dropdown menu that looks up a list in ACTIVE, locates a desired value, and displays several cells contents in ACTIVE, - in the INPUT page.
This is working.

Question 1:
One cell in ACTIVE contains "comments" - how do I display the comments in the cell from ACTIVE, in a cell in INPUT - so I can look at all of them.

Question 2:
How do I update the contents of a cell in ACTIVE, at the same location that was selected from the dropdown.

I am a beginner - please help.

See present MACRO below...

Sub UpdateLogWorksheet()

Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCopy2 As String
Dim myCell As Range

'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D6,I18,D20,D19,D8,D10"
myCopy2 = "D5,D6,D7,D8,D9,D10"
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Payments")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yy"
End With
.Cells(nextRow, &q... Read more

A:Solved: Excel Macro Dropdown Menu Cell Contents

Read other 16 answers
RELEVANCY SCORE 83.6

Hello,

I have 2 data sheets in an Excel workbook that have ~8000 rows of data each. Each row has 2 columns. Col A is an entity ID and Col B contains a set of long string values (i.e., institutional affiliation and location), each value separated by a semicolon (. More specifically, Col A is a paper ID#, Col B includes the institutional affiliations for each author.

What I need to do is to take the distinct values in Col B and separate them into individual rows for data processing. Also, when I separate the values, I need to retain the relationship between the values in Col B and Col A. That is, I need to put each institutional affiliation in its own row, but ensure that the relationship to the paper remains intact. Put another way, I want to cut everything that follows a ; in Col B, insert a row, paste what I cut into the next row in Col B, and then go back up one row, over one cell, copy the unique ID, go down one cell, and paste.

So here is my problem: when I execute the find function in Excel 2008 and search for the semicolon, it returns the entire cell. Normally, if one executes a find command, it highlights the character. The next logical step would be a shift+end to highlight the remainder of the text to the end of the line. This does not work because the find command returns the entire cell. Does anyone have any advice on how to fix this? Right now I am thinking I need to export the data into another program, execute the find and replace, and then re-import the data... Read more

A:Solved: Excel macro to separate entries in a single cell

Read other 13 answers
RELEVANCY SCORE 83.6

I have a macro that adds a blank row above the active row. It then sequentially numbers column A (starting with A9) down to the last row that contains data in column C. How do I get the (macro) to return to the cell that was active when the macro was run.

Alternately how do I get the (macro) to return to the beginning cell (or one directly above or below) when the active row is deleted.

These are the sections I need help with (I think...)

Sub InsertRowAbove()
Selection.EntireRow.Insert
Dim LR As Integer
Columns("A:A").Select
Range("A9").Select
ActiveCell.Formula = "=Rows(A$1:A1)"
LR = Range("C" & Rows.Count).End(xlUp).Row
Range("a9").Select
Selection.AutoFill Destination:=Range("A9:A" & LR), Type:=xlFillDefault
Range("A9:A" & LR).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("b9").End(xlDown).Offset(1, 0).Select

End Sub

Sub DeleteActiveRow()

Selection.EntireRow.Delete
Dim LR As Integer
Columns("A:A").Select
Range("A10").Select
ActiveCell.Formula = "=Rows(A$1:A2)"
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("a10").Select
Selection.AutoFill Destination:=Range("A10:A" & LR), Type:=xlFillDefault
Range("A10:A" & LR).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, O... Read more

A:Solved: Return to starting cell after running a macro in excel

To go "home" at "the end", you can always:

x = ActiveCell.Address
'do other stuff
Range(x).Select

But what is:

Columns("A:A").Select
Range("A9").Select
ActiveCell.Formula = "=Rows(A$1:A1)"

? For that you only need:

Range("A9") = "=Rows(A$1:A1)"

What is the formula meant to be doing?

TIA
 

Read other 2 answers
RELEVANCY SCORE 83.6

Hi all,
I have a friend who's having a problem with a macro and it's been WAY too long since I've worked much with macros so I told her to post here. She's intimidated by you guys so I told her I'd be her proxy, but I'll send her a link to the thread and try to flush her out if you need clarification. Basically, she has a pivot table which produces a grand total. When the pivot table is refreshed, the grand total cell reference can change (for example, from D16 to F23). She has a formula that finds the reference of the grand total cell:

=CELL("address",INDEX($B:$AZ,MATCH("Grand Total",$B:$B),MATCH("Grand Total",$B2:$AZ2)))

She would then like to have the macro select the grand total cell, based on the reference provided from the formula, so that the macro can continue on using that grand total cell reference. I'm sure I've made a mess of the question, so let me know if I'm not making any sense. Thanks!
 

A:Solved: Excel macro: selecting a cell from a formula reference

Read other 12 answers
RELEVANCY SCORE 82.4

I have an issue if certain cells are blank (not sure if they would be null, blank, empty) to have a macro to input "0" in the cell instead. When I look at the format in excel it's under general.

I currently have the following and it puts nothing in the cell. (or seems to be nothing in the cell) I can manually enter a "0"(zero) in the cell and it shows.....

Sub Addifblank49()
Sheets("BPS").Select
Range("c16").Select
Var = Selection.Value
If IsNull(Range("C16").Select) Then Var = "0"
End Sub
I've tried using val instead of var and it won't let me. I'm not real familiar with VBA so any help would be awesome!!
 

A:Solved: Excel Macro - enter in data in cell if Null/Empty

Read other 10 answers
RELEVANCY SCORE 82.4

Hoping to get some help on this question please. I have reviewed the forum and found a similar post, but the sample data is more predictable than mine and the Macro posted doesn't work on my data. See post "Excel Macro to Move data in Rows to column" 28-Aug-2008, 05:47 AM".

On my data, I would like to be able to take the yellow row, and move the data up to the previous row starting in column "R". The rows I would like on the same line, all begin with a date. I don't know how to make a looping Macro that will identify the "date lines", and take the "second date line", and move it to the "first date line's" row.

Any help would be appreciated.
I have attached some sample data. (I hope that's okay?!)

Thank you!!!!
Christine

PS - The range on data varies.
 

A:Solved: Excel Macro to sort data by date, then move to new cell

Christine,

this macro below works well with the sample data.
Code:
Sub sorting()
Dim Rng As Range, c As Range, i As Long
Set Rng = Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
For Each c In Rng
i = (i + 1) Mod 2
If i = 0 Then
With c.Resize(, 15)
.Copy .Offset(-1, 17)
.ClearContents
End With
End If
Next
End Sub

How it works:
1. Takes all those cells in column A of the active sheet that contain numbers. (Dates are actually numbers, only formatted to look like dates.)
2. In a loop, it takes every second cell of the set of cells selected in Step #1.
3. Takes a 15 cells wide region starting with the cell selected in Step #2, and copies it to the region starting with the cell positioned one row up and 17 columns to the right.
4. Deletes the content of the original 15 cells wide region.

Caution:
1. If there are any numbers in column A besides dates, then the algorithm is wrong and the macro will fail.
2. If the dataset in the second date line is wider than 15 cells wide then the rest will remain in place. But the macro can be easily updated, just change the Resize line.
3. Don't run it twice on the same worksheet.
4. Always make a backup copy of your data before running the macro, because it is not Undo-able.

Jimmy
 

Read other 3 answers
RELEVANCY SCORE 81.6

Excel Masters, I am not sure how to loop through the following code for multiple row formatting. The code works fine if the user has selected cells within a single row. However, when multiple rows are selected, the 'With ActiveCell' formatting only works for the first row selected. How can I loop the 'else' statement to format the ranges for all rows with a cell selected?
Private Sub Button_DeleteRow_Click()
Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then

Selection.EntireRow.Delete

Else

Selection.EntireRow.Interior.ColorIndex = xlNone
With ActiveCell
Range(Cells(.Row, "AA"), Cells(.Row, "AN")).Interior.ColorIndex = 15
Range(Cells(.Row, "c"), Cells(.Row, "d")).Interior.ColorIndex = 15
Range(Cells(.Row, "a"), Cells(.Row, "a")).Interior.ColorIndex = 15
Range(Cells(.Row, "j"), Cells(.Row, "j")).Interior.ColorIndex = 15
Range(Cells(.Row, "n"), Cells(.Row, "n")).Interior.ColorIndex = 15
End With

End If

End Sub

On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.
 

A:Solved: Excel Macro - Loop selected-cell formatting for multiple rows

Something like:

Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then

Selection.EntireRow.Delete

Else

Selection.EntireRow.Interior.ColorIndex = xlNone
SelRows = Selection.Resize(, 1).Cells.Count
Cells(ActiveCell.Row, 27).Resize(SelRows, 14).Interior.ColorIndex = 15

'(and so on)

End If

?

On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.Click to expand...

If you mean "how do I bypass that for Selection.EntireRow.Delete then:

Application.EnableEvents = False
Selection.EntireRow.Delete
Application.EnableEvents = True
 

Read other 2 answers
RELEVANCY SCORE 81.6

Hello everyone,

I am completely stumped and I hope to find some help from someone here... I am trying to format an excel spreadsheet that has over 10k records is a very strange format. Please see attachment...

I have created the logic to grab the numbers to the left of the "carrot" in cell E3 and replicate that number in cells B3 B4 B5 B6 B7 B8 B9 B10. That process is replicated to pull the numbers to the left of the "carrot" in cell E13 and replicate that number in cells B13 B14 B15 B16 B17 B18 B19 B20...And so on and so fourth. The problem I am stumped on is how do i replicate this process all the way down the spreadsheet?

One FYI is that each record "bock" is not always (as in the example spreadsheet) 10 lines it can be 11/12/13 or more lines. Basically the record starts at the letters "MSH" (see A1) So I the logic I am thinking of is to push the code in red in the "E" cells to the "B" column after the "EVN" (in cell A2) and to stop before the "MSH" (in cell A11) and continue that process down the spread sheet.

I hope this is not too confusing but I have been banging my head on the wall for hours and just cant figure it out. Thank you all for your help
 

A:Solved: Very hard macro in Excel (copy part of one cell to several areas with logic)

Read other 8 answers
RELEVANCY SCORE 81.6

Our department receives an Excel spreadsheet with a multitude of sheets on a regular basis from another department. Some of the cells are formatted as "General" and others as "Text". One of our departments macros searches for certain instances of a number on the Logic and Flow columns such as the number "1". In doing so, the macro finds "1", "21", "102" and so on. Or if seaching for the number "20", it finds "20", "120", "201" and so forth. I would like to run a macro on each sheet that would, if need be, change all of the data in the Logic and Flow columns to Text and then add leading zeros to make each of the numbers in the cells 3 digits long. In my example, Cell B2 original has a 4 and a 2 (4 2). I want to end up with 004 and 002 (004 002). In my example, Cell B8 original has a 20AT. I want to end up with 020AT.

In my attached example, Columns B & C are what we get from the other department. I want to end up with what is shown in columns F & G.
 

A:Solved: Add zeros to excel cell formatted as text

Read other 16 answers
RELEVANCY SCORE 81.6

I want a name list in Excel and want to add all the names to a total.

can I have the formula to total each cell to the value of 1 when there is text in the cell?

Thanks
 

A:Solved: (Excel)Can I use text in a cell and make it equal to 1

Try a formula like the one below which will count all the non-blank cells in the range A1 through A100

=COUNTA(A1:A100)

Rollin
 

Read other 2 answers
RELEVANCY SCORE 81.6

Hello,

I am trying to find a way to get Excel 2003 to allow a large amount of text be placed into one cell without restricting the vertical size of the cell. I am importing several fields, one of which is a comments section that often has up to 500 words. However, when it dumps into Excel, the default is for the cells to be expanded horizontally, not vertically, and I'm limited on how tall I can make the text cells until it no longer accommodates me and just keeps the text at a fixed height, while the width is ridiculously long.

Is this something that I can manipulate somehow? It seems to do this no matter if cells are merged or not, but the way.

Thanks in advance for the help.
 

A:Solved: Limited Excel cell capacity for text

Read other 11 answers
RELEVANCY SCORE 81.6

I have a large workbook (over 4 meg) with multiple sheets and lots of formulas. Today, I was trying to change a cell formula and instead of placing the result in the cell, the formula was placed in the cell and displayed as text, complete with the '=' sign in pos 1. The Evaluation tool states that the cell contains a constant.

The following is an example. The original formula and the mod with the changed component in red.

Original cell formula: =SUM(INDIRECT("T$"&MATCH($V1265,$V$2:$V1265,0)&":T"&ROW())). The cell result is a number.

modified cell formula: =SUM(INDIRECT("T$"&MATCH($V1265,$V$1:$V1265,0)&":T"&ROW())) . The cell displays the formula, exactly as shown, as text and therefore no result.

Has anyone ever encountered this before and is there a solution?
Thanks, Tom
 

A:Solved: Excel 2003: Formula Cell Becomes Text After Mod.

Read other 7 answers
RELEVANCY SCORE 80.4

Hi,

I'm creating a procedure using Excel. Each cell is used to list an action step and includes reference to a procedure which resides on the business's website. I want to add a hyperlink to the reference text title only.

I can only seem to create a hyperlink for the entire cell, but some of the steps refer to two separate procedures, hence I need to create the link to the text.

PS. I've used Excel, rather than Word, as I'm using formulas to then sort and filter various steps.

Many thanks in advance!
 

A:Solved: Creating a hyperlink for text/icon WITHIN an Excel cell

 

Read other 3 answers
RELEVANCY SCORE 80.4

Hi ther guyz............i a have small problem with the exel sheets...........
my problem is e.g. i have alot of cells with a number and some text in it........and they both are in one/single cell (e.g. "878 queen st" in A1)....now what i want is to remove let's say "queen st" from each cell so that there is only number left in it.....................i cannot figure out any formula........if there is any..............need help imediately
 

A:Solved: EXCEL: How to diffrentiate Text & number from a single cell????

Read other 7 answers
RELEVANCY SCORE 80.4

Ok I have tried to figure this one out myself and to no avail.

What I am trying to do is create a macro that would add text at the end of the text in a current cell.

i.e.

Company 1
Company 2
Company 3

I am trying to create a macro that would would go thru each cell and add "Total" to the end.

Company 1 Total
Company 2 Total
Company 3 Total

Every time I try and create a macro, it copies the previous text into the current cell.

Thanx in advance.
 

A:Solved: Excel Macro Help - Adding Text

Read other 15 answers
RELEVANCY SCORE 80.4

Good day,

I have an inquiry, I wanna ask if there is a way to get the contents of .txt #1 file and place it in a sheet in excel starting from cell A2, then the macro will look for the end of the copied .txt file in excel...offset it by 3 cells down then paste the contents of .txt #2.

Then the macro should also be able to delete the pasted values from .txt #2 and replace it with .txt #3 at any time should the user choose to do so still making sure that it's below .txt #1.

The data is actually being used by a pivot table, it would just refresh everytime we have a new .txt file coming in without replacing the first part. Oh...i don't have Access on my system, I only have VBA in Excel and the text files to use.
 

A:Solved: Text files and Excel Macro

Read other 6 answers
RELEVANCY SCORE 80

Hi all,

I have done a decent amount of Java, but VB is slightly confusing to me.

I want to be able to write a loop which will copy data, transpose it and then paste it. It needs to do this several hundred times, with each copied data-set being on a new row.
Code:
Dim Num As Integer


For Num = 1 To 10 'start loop

'cut source cells
Sheets("14402-ANSWERS-07-27-05-2").Select
Range("A2:C33").Select
Selection.Cut

'Paste-Copy-PasteSpecial
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Selection.Copy
Range("A34").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

'Delete Source Rows
Sheets("14402-ANSWERS-07-27-05-2").Select
Rows("2:33").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

Next Num ' end loop

'Autofit the data
Sheets("Sheet1").Select
Columns("A:AG").EntireColumn.AutoFit

(nb The reason for the cut-paste-copy-pastespecial is because Excel won't cut and paste-special across different sheets.)

As you can see, I'm nearly there, but currently all it does is paste each new set of data over the existing one in cell A34. How do I make it increment the destination row by 4 or 5 on each iteration?

This is incredibly simple, but it won't let me j... Read more

A:Solved: Changing a Cell Reference: Noob question in VBA/Excel

You use Activecell.Offset(r,c).select to move from the current cell. You increment r for rows and c for columns.
Excel has no problems with "Paste Special Transpose" when moving from sheet to sheet. I just recorded this Macro of doing just that -
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
 

Read other 2 answers
RELEVANCY SCORE 76.4

In cell j, I have formula =IF(SUMPRODUCT(ISNUMBER(SEARCH("VLXP",K2:AB2))+0)>=1,"Yes","No") that returns yes or no if VLXP is contained in any cell K2 through AB2 and it works correctly. What I would really like to do is then put into cell j the entire matching cell content or if not found return n/a. Is there a way to accomplish this maybe with VBA?
 

A:Solved: Excel if cell contains vlxp then put matching cell data in current cell

Read other 6 answers
RELEVANCY SCORE 75.6

Hi there,
I'm attempting to make a macro in an excel workbook which will find the first unused cell in a given row and then select it, so that I can have the user input whatever information it is that they wish to input. I've got that second part of the problem figured out, but I cannot seem to figure out good code which will select the last cell in the row. I found on another webpage this code:
LastRow = Range("A6000").End(xlUp).Row
which apparently uses the End function to find the last cell in a given column and then names this cell "Last Row". This is basically the opposite of what I am trying to do, so I attempted to reverse most of what was going on in this line of the code:
LastColumn = Range("BJ1").End(xlLeft).Column
However, this code, and any variation of it which combines End and Column always ends up encountering a bug in the code and failing when used. Could anyone lend me some support here? I'm really hurting for it.
 

A:Excel Macro which Selects Last cell in a Row?

Read other 8 answers
RELEVANCY SCORE 75.6

I need to export the contents of one and the same cell to 100 different text files.

For example... I need the contents of Sheet1 Cell A1 to be exported to new text files. The names for each text file is on Sheet2 Cell A1 - A100.

Does anyone have a code example for this? I have search and found some similar but i couldnt figure out the code to modify it.

Thank you in advanced.

asm
 

A:Export Excel Cell with Macro

Hi asm,
and will the exported file be a new one or can it be an existing text file?
If the file is already presnt then what? Delete the file or append the new data to it?
Which version of EXCEL are you using?
Macro's a problem ?
 

Read other 1 answers
RELEVANCY SCORE 75.6

Hello!

I am new to this site and to the world of macros. I am creating a form in Excel for my company and cannot find any info for creating a macro for hyperlinked info.

Background of my form:
It's a time off request form. My dad (who knows a lot of Excel and macros (but not this kind)) has helped me get to where I am at this point.

Currently I have a dropdown for employee names. Once a name is selected, it automatically fills in their manager and department number. I have a Reset Form button to clear info out and would like to create a Submit Form button that would send the form to the manager. Is something like this possible?

I'm guessing it would need to pull from my Tables sheet.

Help! I hope I made some sense.

P.S. I will attach a sample of what I have so far.
 

A:How to have a macro add hyperlink to a cell in Excel

Read other 9 answers
RELEVANCY SCORE 75.6

I'm trying to get the cell (row & column) where a checkbox is at a excel sheet, using VB code in an excel macro.
the idea is to modify the cell properties (color, fonto, other) when the checkbox is true, and that will be only for that particular cell (where the checkbox is placed).

Any ideas?

Thx.
 

A:CheckBox cell in Excel Macro

Read other 7 answers
RELEVANCY SCORE 75.6

Hi all,
i am trying to create a summary page for a test document, each module of testing has been given its own tab in the workbook and i want to use a macro to:
1, look up the correct tab
2, find the latest test run that has been finished
3, return the test round number, the number of tests run, and the number of tests that passed.
I am using Excel 2003 edition.

I can make the macro the problem is i have to make 3 SEPERATE macros to return the 3 values. I would like to only have one that can return all three values.

the following ths the macro that i have so far:

Option Explicit
Private m_iColumn As Integer 'm_iColumn currently on
Private Const m_iColumnJump = 7 'number of m_iColumns in between test runs
-----------------------------------------------------------------------------------------------------
Function Last_Test(Round_lookup As String)
m_iColumn = 3
Do Until Worksheets(Round_lookup).Cells(3, m_iColumn) = "0" Or Worksheets(Round_lookup).Cells(3, m_iColumn) = vbNullString
'runs until finds nothing for a test run
If Worksheets(Round_lookup).Cells(3, m_iColumn) = "0" Then
'looks to see if cell contains a testrun if it doesnt then
Else
m_iColumn = m_iColumn + m_iColumnJump
'adds 7 to the m_iColumn number
End If
Loop

If m_iColumn = 3 Then
Last_Test = "Test run not started."
Else
Last_Test = Worksheets(Round_lookup).Cells(1, m_iColumn - 7)
'the last m_iColumn is the name we want
End If
End Function
---------... Read more

Read other answers
RELEVANCY SCORE 74.8

Hi again

I'm doing a complicated macro that seems to run for about 5 minutes. I believe I can shorten it if only I knew how to do the following.

My macro starts at Cell A2 and scrolls down column A until a certain condition is met. The condition is that the active cell <> cell D1. The macro then stops at the active cell in column A and then copies the active cell in cell D1. More code goes here. The program then goes back to the top of column A to Cell A2 and scrolls down again until the active cell <> cell D1.

Is there a way that I can continue where I have left off in Column A instead of going back to Cell A2 and scrolling down again? Here is a concrete example. Copy A3 to D1. Code goes here. Loop until activeCell <> D1. The cursor stops at A10. A10 is now copied to D1 and the process starts all over again. The macro starts back at A2 and scrolls down. Is there a way that the macro can put the cursor back at A10, where the copy started from??

The next spot the cursor stops at is at cell A16 and then copies A16 to D1. I now want the cursor to go back to A16 instead of A2.

I hope that you understand what I'm talking about.

Mario
 

A:Do a macro in excel that return to a prevoius cell.

Read other 10 answers
RELEVANCY SCORE 74.8

Hi,

I have a large excel worksheet, divided into 4 rows (different data fields). Hard luck, in one of the rows, some cells contain two products or more instead of one (ex: "ZB3054066ZB3601207ZB3601206"). They are separated either by a blank or by a (double bar).
I need that each time I detect a cell in that particular row that contains multiple products, I create new rows so that each row contains only one product in the column "component parts" and that the original data in the other columns remains unchanged.
I don't know anything about excel macro, but here is the approximate code I created to do this task :
If current row cell "component parts" contains a separator (either or blank),
{
create "number of separators" new rows
copy every field except the "component parts" one
while( cell "component parts" contains a separator)
{
cut the part of the "component parts" cell before the first separator (including the separator)
paste it into the next row "component part" cell
delete the separator in the new "component parts" cell
}
}

If anyone knows how I could do this in an excel macro...
Thanks.
 

A:Excel macro : cell with multiple content

Read other 16 answers
RELEVANCY SCORE 74.8

Microsoft Excel MS Excel 2003

I would like to pull information from worksheet cells (specifically file path and name) to use in an auto open macro in order to provide a workaround on the limitation of the INDIRECT function's inability to access data in closed worksheets.

How do I create a reference in the Visual Basic macro to pul the data from the worksheet?
 

A:MS Excel Worksheet Cell info to be used in Macro

Read other 10 answers
RELEVANCY SCORE 74.8

I'm creating a macro to paste data from one worksheet into another and I need to find the last unmodified cell in a column to copy the date. The last cell will be modified by having the fill color changed. All cells with data have the fill color modified although not all cells with fill color have data.

I would use something like
While Cells("i,j") <> "",
but not all cells have data.

I need some VB method which can look at a cell and determine if it has No Fill or is filled with a color. Then I will just search for the first cell in a column which has "No Fill" and that will be the end of the data.

Hope this makes sense and thanks in advance for any help you can provide.
 

A:(ttt) Excel Macro finding last unmodified cell

Read other 7 answers
RELEVANCY SCORE 74.8

In the "Checkout" tab, I am wanting to copy rows if/when a cell (column "B") in that row is greater than zero. I need this macro to go to the next row and the next row and the next row until the cell in column "B" is blank. Once the selection has been copied, I need it to pastespecial only the values and not fomulas in the "Database" tab.

I have attached an example spreadsheet.

Any and all help is greatly appreciated.
 

A:Excel Macro to copy row if cell is greater than zero

This is a duplicate of your other post
 

Read other 1 answers
RELEVANCY SCORE 74.8

hi

my macro tells cell A2 on sheet1 to equate to cell D10 on sheet2. D10 on sheet2 is a total from the same sheet (sheet2) and sometimes can move to D8, D9 or D11. how do i incorporate the moving target cell into my macro?

tim
 

A:Excel macro, moving target cell

Read other 6 answers
RELEVANCY SCORE 74.8

I selected a cell, and then recorded a macro when I copied the cell, moved down to the cell below it and pasted it. But when I select another cell, and then run the macro, it goes to the initial cells I did when I recorded the macro.

So is there a macro where I have selected a cell, whatever cell I decide, and then run the macro, and it copies the cell selected and paste it to the cell below the cell that was copied?
 

A:Solved: Macro to Copy and Paste From One Cell to Cell Below

Read other 16 answers
RELEVANCY SCORE 74

Hi,

I would like to be able to input a number into a cell B3 on sheet 1 - 'Head Count'. Then get the macro to look up the number that was input and copy and paste all the rows relating to it from sheet 2 - 'Data' and paste them on 'Head Count' next to the input cell.

I am having trouble with getting the macro to copy and paste depending on the target cell. This is what i have so far:

Sheets("Data").Select
'Copy cells of Data from rows containing cost centre in col G of the active worksheet (source sheet) to cols
'of Head count (destination sheet)
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Head Count")

Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long
sCount = 0
dRow = 1
For sRow = 1 To Range("G65536").End(xlUp).Row
'use pattern matching to find "Significant" anywhere in cell
If Cells(sRow, "G") Like "Worksheets("Head Count")Range("B3")" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols

' With Range("A14:AA14", Range("A65536").End(xlUp))
'.Paste

Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A")
Cells(sRow, "B").Copy Destination:=DestSheet.Cells(dRow, "B")
Cells(sRow, "C").Copy Destination:=DestSheet.Cells(dRow, "C")
Cells(sRow, "D").Copy Des... Read more

A:Excel macro copy and paste depending on a cell

Can you upload a workbook with dummy data so we can see how you've structured the data.
 

Read other 1 answers
RELEVANCY SCORE 74

I'm trying to write a macro for Excel 2010 to count cells only if the background fill color is a certain color. I'm having trouble finding how to access the background color information in Excel 2010.
 

A:Excel 2010 - Macro to count if cell is a certain color

Read other 6 answers
RELEVANCY SCORE 74

Greetings -

Given:

Target Complete Money
A B C
1 1/1/09 1/7/09 50.00
2 1/5/09 1/12/09 100.00
3 1/7/09 1/14/09 50.00
4 1/17/09 1/17/09 25.00
5 2/2/09 1/26/09 25.00
6 2/5/09 150.00
7 2/6/09 150.00
8 2/5/09 75.00

Currently i have the following formula to calulate how much money that I might make.

{=SUM((MONTH(A2:A100)=1)*C2:C100)}
This works fine as I have no blank cells in column A

If I try to apply this formula to column B I get an incorrect number as I have blank cells in there with no values.

Is there a way to continue to use this formula but have it check for blank cells and if it finds any, don't add those values??
Thanks!
 

A:Excel 2007: Date/Sum Macro/Blank Cell

Welcome to the board.

How about the attached? Contains 1 "master" dynamic named range & 2 others based on the "master". Use E2 to define the month you're after.
 

Read other 3 answers
RELEVANCY SCORE 74

Hy guys

2nd time i am posting stuff for help, and as i was helped before i will again look forward the response.

I have a file of excel, in which i am sending emails to different candidates of admission, with scan letter placed in the same folder by name.

I want to edit this code, which could select attachment based on Column A list adjacent to the email address

I am attaching the file also pasting the code

Sub Test1()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2013
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then

Set OutMail = OutApp.CreateItem(0)

strbody = "We at Graduate School of Engineering Sciences and Information Technology are extremely pleased to know that you have selected Hamdard University as preferred choice for your graduate/post-graduate Studies. " & vbNewLine & vbNewLine & _
"Hamdard University is a pioneer Higher Education Institute (HEI) of Karachi producing Masters and PhDs in the fields of Engineering, Computer Sciences, Information Technology, Energy and Environment since 19... Read more

A:Attachment based on cell value in a excel email macro

anybody ???
 

Read other 2 answers
RELEVANCY SCORE 74

update on the below question:I think something is wrong when I opened the .csv file.If I leave the file open then run the macro, it works ok. If I open the file within the macro, I get the format problem.Any advice?Thanks!Hi,

Please can someone give me some advice on this?

I am writing a vba code in Excel 2003 to copy from a source .csv file and paste into xls file.

First, I tried this using macro recording, it worked fine. so I recorded the macro and tried to apply it. Something strange happened.

One of the columns is date, and originally is format Date (*01/01/01). If I manually copy and paste, the format is fine. If I use the macro, some (and only some) of the cells in this column will change format to General and align to the right, while the others keep the original Date format and align to the left.

How did this happen?

I viewed the .csv file using Notepad, and cannot see any difference between these dates that end up in different format.
And if I manually copy/paste, there will be no format issue at all.

Please can someone help?

Thanks!
 

A:copy/paste in excel macro changes cell format

upon a closer look, i think I found some clue, but still far from a solution, here is what I observed.

for date 09/07/2006, excel interpret it as Sept 07 06
for date 20/06/2012, excel interpret it as June 20 2012

please note the year format is different!

any advice pls?
 

Read other 1 answers