multiplying cells vba

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Multiplying cells on VBA

Multiplying cells on VBA

resolvedResolved · High Priority · Version 2010

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

 

Training courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Access tip:

Convert A Form Into A Report

If there is a form that you want to to save as a report:

1. Open that form in Design View
2. Select File and Save As
3. In the Save As Dialog box Select Report

The system creates a report based on the form.

View all Access hints and tips


Server loaded in 0.12 secs.