• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiply a range according to a criteria - VBA

Maria Clara

Maria Clara

New Member
Hello All,


I'm new to VBA and I'm trying to write a code for the spreadsheet below but so far I had no success.


http://www.sendspace.com/file/mu39la


What I need to do is to multiply all values in each column by the number on column M if the values on range B2, C2 and so on are marked as 'International'.


I think it is important to mention that sometimes I might have less than 10 columns to do this calculation and sometimes more, so I guess I'd need a code that shows something as 'As long the range that starts on B2 (it will always start on B2) is not empty and as long as column A is not empty then all values should be multiplied by cell M1 - if they are 'International'.


I'm not sure If I was clear but I hope so.


Thank you in advance for the help.
 
Hi Maria Clara,


Welcome to the Forums! Please try this (Place in a Vba Module & Run)

[pre]
Code: 
Sub multiply()
Dim i As Integer, j As Integer
Dim LastRow As Long, LastCol As Integer
Dim M As Double

LastRow = Worksheets("Plan1").Cells(Rows.Count, 1).End(xlUp).Row - 1
LastCol = Worksheets("Plan1").Cells(2, Columns.Count).End(xlToLeft).Column
Worksheets("Plan1").Activate
M = Worksheets("Plan1").Range("M1").Value

For j = 2 To LastCol
For i = 2 To LastRow
If Cells(2, j) = "International" Then Cells(i + 1, j).Value = Cells(i + 1, j).Value * M
Next i
Next j
End Sub
[/pre]

Hopefully it works..


Regards,
 
Hi Faseeh, thanks a lot for your response.


I've tried to run it but I'm getting an error on the sentence below after the 'Then'.


The message it's showing is 'Error in execution time 13': Incompatible types.


If Cells(2, j) = "International" Then Cells(i + 1, j).Value = Cells(i + 1, j).Value * M


Could you help me to fix it?


Thank you again.
 
Hi Maria ,


The problem is because of cells which are blank ; change the above statement to :

[pre]
Code: 
If Cells(2, j) = "International" Then Cells(i + 1, j).Value = IIf(Cells(i + 1, j) = "", 0, Cells(i + 1, j).Value) * M
[/pre]
Narayan
 
Hi Narayan, thanks sooo much! It works just fine!


I just have two more questions (sorry for so many).


- One would be that the blank cells are now showing as 0, is there a way I can erase it and change it back to blank/empty on the same code?


- And the other one would be that I need the number with two decimals, for eg. after the calculation I would need the number 172,8064 to show as 172,81 only. Would be possible to do this?


Appreciate your help!!
 
Hi Maria ,


For both , change the above statement to the following :


If Cells(1, j) = "International" Then Cells(i + 1, j).Value = IIf(Cells(i + 1, j) = "", "", Format(Cells(i + 1, j).Value * M, "#.#0"))


Narayan
 
Hello again, Narayan.


I've change the code but when I try to execute nothing happens ... is that normal or am I doing something wrong?


Below is the link with the file updated. Thank you!!!


http://www.sendspace.com/file/bsu5tp


Also, I'd like to study more about this, is it called For Next loop? What about the IIf?


Regards!
 
Hi Maria ,


Can you copy + paste the code which is here :

[pre]
Code: 
Sub multiply()
Dim i As Integer, j As Integer
Dim LastRow As Long, LastCol As Integer
Dim M As Double

LastRow = Worksheets("Plan1").Cells(Rows.Count, 1).End(xlUp).Row - 1
LastCol = Worksheets("Plan1").Cells(2, Columns.Count).End(xlToLeft).Column
Worksheets("Plan1").Activate
M = Worksheets("Plan1").Range("M1").Value

For j = 2 To LastCol
For i = 2 To LastRow
If Cells(i + 1, j).Value <> "" Then
If Cells(2, j) = "International" Then
Cells(i + 1, j).Value = Format(Cells(i + 1, j).Value * M, "#.#0")
End If
End If
Next i
Next j
End Sub
[/pre]
Narayan
 
Great! Thank you soo much!


Can you give a tip about the numbers? After I run the macro the numbers are showing as text, is there a formula to change it back to numeric so as I don't have to do it manually?


Thank you again!


Below is the file updated: http://www.sendspace.com/file/4yrr2o
 
Hi Maria ,


I think the problem is because of your version using the comma "," as the decimal point , instead of my version , which being English , uses the period "." as the decimal point.


In the Format function , I have used :


Format(Cells(i + 1, j).Value * M, "#.#0")


I think you might have to use :


Format(Cells(i + 1, j).Value * M, "#,#0")


Can you try this and let me know ?


When I run the macro , I don't get the values converted to text ; they remain numbers.


Narayan
 
Narayan, I've just tested as you said and it works PERFECTLY! Thank you SO MUCH for your help!


Faseeh, thank you for your help too!


Regards!
 
You must log in or register to reply here.
Back
Top