Silvia has attended:
No courses
Multiplying cells on VBA
Hi, I am trying to multiply two cells from column D and E and I want the result to show on column F and run through the whole sheet. But, for some reason is not even running and the cell just appear blank. Can someone tell me what i did wrong, please?
Sub TotaCost()
Range("F2").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Offset(0, 1).Value > 0 Then
ActiveCell.FormulaR1C1 = "RC(-2)*RC(-1)"
ElseIf ActiveCell.Value = 0 Then
ActiveCell.Offset(0, 1) = "N/A"
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
RE: Multiplying cells on VBA
Hello Silvia,
Thank you for your post. Looking at your code, it looks like you are referring to data in Column G (If ActiveCell.Offset(0, 1).Value > 0 Then). Is this correct?
If not, I'm assuming there is currently no data in Columns F and G and only data in Columns D and E.
Would you like your code to do the multiplication if the value in Column E is > 0 and to say "N/A" if the value in Column E is = 0?
Please let me know if this is correct, otherwise it would help if I knew where the existing data is located.
Kind regards
Marius Barnard
STL
RE: Multiplying cells on VBA
Hi Marius,
Your last statements are correct. There is indeed no data in Column F or G. I was trying to get the results to show up in Column F.
And, yes I would like the code to do the multiplication if the value in Column E is > 0 and to say "N/A" if the value in Column E is = 0 .
Thanks so much and happy new year. :)
RE: Multiplying cells on VBA
Hi Silvia,
Happy new year to you too!
Here is some code which might do the trick:
Sub TotaCost()
Range("F2").Select
Do Until ActiveCell.Offset(0,-1) = ""
If ActiveCell.Offset(0, -1) > 0 Then
ActiveCell = Activecell.Offset(0,-2)*Activecell.Offset(0,-1)
ElseIf ActiveCell.Offset(0,-1) = 0 Then
ActiveCell = "N/A"
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
I hope this helps!
Kind regards
Marius
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
RE: Multiplying cells on VBA
Hi Marius,
thanks for it.
One last thing, I want to add another condition but this time it has text. And of course, it is not working. can you help.
Sub TotaCost()
Range("F2").Select
Do Until ActiveCell.Offset(0, -1) = ""
If ActiveCell.Offset(0, -1).Value > 0 Then
ActiveCell = ActiveCell.Offset(0, -2) * ActiveCell.Offset(0, -1)
ElseIf ActiveCell.Offset(0, -1) = "NOT FOUND" Then
ActiveCell = "N/A"
ElseIf ActiveCell.Offset(0, -1) = 0 Then
ActiveCell = "N/A"
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
RE: Multiplying cells on VBA
Hi Silvia,
Try:
Sub TotaCost()
Range("F2").Select
Do Until ActiveCell.Offset(0, -1) = ""
If ActiveCell.Offset(0, -1).Value > 0 And IsNumeric(ActiveCell.Offset(0, -1).Value) Then
ActiveCell = ActiveCell.Offset(0, -2) * ActiveCell.Offset(0, -1)
ElseIf ActiveCell.Offset(0, -1) = "NOT FOUND" Then
ActiveCell = "N/A"
ElseIf ActiveCell.Offset(0, -1) = 0 Then
ActiveCell = "N/A"
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
