98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Multiplying cells on VBA
Multiplying cells on VBA
Resolved · High Priority · Version 2010
Sat 30 Dec 2017, 21:19Delegate Silvia said...
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
Tue 2 Jan 2018, 09:24Trainer Marius said...
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
Tue 2 Jan 2018, 09:28Delegate Silvia said...
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. :)
Tue 2 Jan 2018, 09:49Trainer Marius said...
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
Wed 3 Jan 2018, 17:27Trainer Marius said...
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.
Tue 9 Jan 2018, 11:39Trainer Wendy said...
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.
Tue 9 Jan 2018, 12:02Delegate Silvia said...
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
Wed 10 Jan 2018, 14:16Trainer Jens said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Convert A Form Into A ReportIf there is a form that you want to to save as a report: |
