VBA : simple IF then multiply by cell value

[azm]

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?

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

 

[Joe4]

The issue is with this part:

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]

The issue is with this part:

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]

I *think* I follow, but am not 100% clear.

Does this do what you want:

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:

Dim s, cell As Range

actually declares "s" as "Variant".

You need to declare it like this:

Dim s as Range, cell as Range

or this:

Dim s as Range
Dim cell as Range