VBA : simple IF then multiply by cell value

azm

New Member
Joined
Jul 7, 2020
Messages
4
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Hello,

I have a loop with a simple IF, but when i try to multiply the value by the value of another cell it gives the error "Run-time error '13' Type mismatch", could someone help me to correct it?

VBA Code:
Dim ShSource, ShDest  As Worksheet
Dim s, cell As Range

Set ShSource = Worksheets("Planogram")
Set ShDest = Worksheets("VM Supports")
Set s = ShSource.Range("E2:CC2")

For each cell in s
If cell = ShDest.Range("D8") Then ShSource.Range("CI2:CI88").Value = cell.Rows("1:87").Value * ShDest.Range("G8").Value
Next cell

End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,045
Office Version
  1. 365
Platform
  1. Windows
The issue is with this part:
VBA Code:
ShSource.Range("CI2:CI88").Value = cell.Rows("1:87").Value * ShDest.Range("G8").Value
I am not sure exactly what you are trying to do. It really doesn't make much sense.

You seem to be trying to set a whole range of cells equal to another whole range of rows multiplied by a single number.
You cannot to do it in that manner. You might need to use SUMPRODUCT formula or a loop.

Can you explain, in plain English, exactly what should be happening in this step?
Please sure to tell us the ranges in your explanation, and exactly what should be happening.
 

azm

New Member
Joined
Jul 7, 2020
Messages
4
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
The issue is with this part:
VBA Code:
ShSource.Range("CI2:CI88").Value = cell.Rows("1:87").Value * ShDest.Range("G8").Value
I am not sure exactly what you are trying to do. It really doesn't make much sense.

You seem to be trying to set a whole range of cells equal to another whole range of rows multiplied by a single number.
You cannot to do it in that manner. You might need to use SUMPRODUCT formula or a loop.

Can you explain, in plain English, exactly what should be happening in this step?
Please sure to tell us the ranges in your explanation, and exactly what should be happening.

Hello Joe4,
my apologies for not having explained clearly.

My loop goes through the range "s" in my first sheet (ShSource.Range("E2:CC2")) to find the cell with the value which matches the value in rangeShDest.Range("D8") in my second sheet; this cell in range s is the header of a column of values (cell.Rows("1:87")). What i need is that when the values of this column are copied in the range ("CI2:CI88"), they are also multiplied by the value of cell "G8".

So for example if the value in "BQ2" = the value in "D8", then the range "BQ2:BQ88" is copied in the range ("CI2:CI88"), and their values (except the header) are muliplied by a number (which is the value in ("D8").

I hope this is clearer and thank you in advance!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,045
Office Version
  1. 365
Platform
  1. Windows
I *think* I follow, but am not 100% clear.

Does this do what you want:
VBA Code:
Sub MyCopyMacro()

    Dim ShSource As Worksheet, ShDest As Worksheet
    Dim s As Range, cell As Range
    Dim n As Long

    Application.ScreenUpdating = False
    
    Set ShSource = Worksheets("Planogram")
    Set ShDest = Worksheets("VM Supports")
    Set s = ShSource.Range("E2:CC2")

    For Each cell In s
        If cell = ShDest.Range("D8") Then
            For n = 2 To 88
                ShSource.Cells(n, "CI").Value = ShSource.Cells(n, cell.Column).Value * ShDest.Range("G8").Value
            Next n
        End If
    Next cell
    
    Application.ScreenUpdating = True
        
End Sub
There is probably a way to do it more efficiently (without looping through all the rows), but I at least want to make sure this is doing what you want first.

Also note that your variable declarations don't actually do what you intend them to. Every variable has to be explicitly declared.
This:
VBA Code:
Dim s, cell As Range
actually declares "s" as "Variant".

You need to declare it like this:
VBA Code:
Dim s as Range, cell as Range
or this:
VBA Code:
Dim s as Range
Dim cell as Range
 

Watch MrExcel Video

Forum statistics

Threads
1,130,444
Messages
5,642,175
Members
417,259
Latest member
gtacw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top