Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I am using Excel 2003 in a Windows XP pro OS.

I am new to VBA programing. I was trying out simple program to learn

some stuff on my own. I wanted my program to add the values of two

rows and put in in third row. The values in the two rows is given by

some formulas. I am getting error in it. I know it can be easily

done by concancate function. But just wanted to get my hand dirty

with VBA.

Please find the progam I had written below. Here I am tring to pick

value from row 7 which has value of # of years adding text "Y" to it

and then adding the value in row 8 which has # of months and then

adding text "M". But I got error while running. I feel it could be

because the value in row 7 and 8 are generate by some formula there.

I put a MsgBox in the code to catch the value. But it didn't show

anything.

Sub WAMYearMonth()

x = 7

y = 2

Do While Cells(2, y).Value <> ""

MsgBox Cells(7, y).Value

Cells(9, y).Value = Cells(7, y).Value + "Y" + Cells(8, y).Value + "M"

y = y + 1

Loop

End Sub

A few things:

Re:"I am getting error in it." and "But I got error while running."

Saying what error would be very helpful.

Re:"I put a MsgBox in the code to catch the value. But it didn't show

anything."

This happens when there is nothing in the cell. Cells(7,2) refers to

cell 7 rows down and 2 columns across, ie. B7.

As your code stands it tries to add (not concatenate) the following 4

things:

what it finds in row 7, "Y", what it finds in row 8, "M"

and it looks to row 2 to see how many columns it should process (I

imagine these are headers?). So as long as there's something in the

column in row 2 it tries to do the concatenating. When it reaches the

first empty cell in row 2 it stops looping.

Now all you need to do is change every instance of a '+' sign in the

code to a '&'. This should stop the Type mismatch error.

Cells(9, y).Value = Cells(7, y).Value & "Y" & Cells(8, y).Value & "M"

I note also that you have the line

x = 7

but never use x elsewhere.

I was using the + sign as I

got it in the code from the Microsoft on-line training program. I am

pasting that code below for you. Here they are trying to concancate

the last name and first name, which are kept in two columns. Please

let me where am I going wrong here.

Sub LoopRange1()

'Have x start at row 3

x = 3

'Loop until a blank row is found

Do While Cells(x, 3).Value <> ""

'This will put the value of the third and fourth columns (C

and D) together,

'with a space between, and into the fifth column (E)

Cells(x, 5).Value = Cells(x, 3).Value + " " + Cells(x,

4).Value

'increase the value of x by 1 to act on the next row

x = x + 1

Loop

End Sub

This code seems fine. The + sign is no problem if the things you are

trying to add or concatenate are all of the same type. In the code

below they're all strings. If they were all numbers and you didn't

have the space in there it would add the numbers together instead.

The & sign is the vba string concatenator. If you had numbers in there

and used the &, the numbers would be treated as strings and

concatrenated.

Didn't find what you were looking for?
Find more on VBA code to add values in two cells which have formulas
Or get search suggestion and latest updates.

Related Topics:

- Copy Value produced by formula in cell C3 to cell D3
- cells containing formula that refer to user-defined VBA function
- Copying current cell's value to another cell
- Formula using range name in VBA code
- clearing cells value without deleting the formula I've made in cell from excel formula , HOW?
- VBA Code for pasting a set of formulas down a data range
- VBA Array - return value to a cell
- Fill VBA form with Cell values of an Excel sheet
- writing vba code to generate vba code
- writing vba code to generate vba code
- Vlookup between two sheets with in a workbook to pick latest value via VBA
- Action taken by Excel when a condition causes a cell's value
- Update a cell based on a DDE updated cell value
- Ctrl+Shift+Arrow Fails To Select Cells When Used in Formula Bar
- Code not updating cell value
- formula returns to multiple cells, without Array object?
- Using an array formula as a hyperlink cell reference
- VBA Formula prob
- Getting a formula to copy from inside VBA
- VBA or array formula?
- formula help but not vba please
- Copy down formula with vba
- VBA variable range in a formula
- VBA code to convert all values of a column in a row separated by semi colon
- Placing VBA Code "Live" Into Another WkBook Via VBA