Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA code to add values in two cells which have formulas

  Asked By: Darcy    Date: Feb 22    Category: MS Office    Views: 5485
  

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

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Geb Chalthoum     Answered On: Feb 22

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.

 
Answer #2    Answered By: Corey Brown     Answered On: Feb 22

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

 
Answer #3    Answered By: Fred Hicks     Answered On: Feb 22

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.




Tagged: