Add formula to dynamic range

[Edgarvelez]

Hi all, never done this before with formulas and need some help.

I have 2 sheets Sh1. & Sh2.
In Sh2. I have a dynamic range in H and I need to apply a formula to the values
The formula is the value in each cell multiplied by 2204.6244202 (*2204.6244202)
I am using case and if in Sh1. D10 from the dropdown is YES then I want to add the formula if the current value of the cell x 2204.6244202

I started some of the code already but have never done this adding a formula to a range.

    Case "Metric Ton|GW|Single Line|Single Line Weight"
    Select Case sh1.Range("D10").Value
    Case "No"
    sh2.Cells(Rows.Count, 8).End(xlUp)(2).Resize(sh1.Range("D30").Value).NumberFormat = "0.000"
    sh1.Range("D26").Copy
    sh2.Cells(Rows.Count, 8).End(xlUp)(2).Resize(sh1.Range("D30").Value).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    Case "Yes"




    sh1.Range("A1").Select
    sh1.Range("D30:D33").Select
    Selection.ClearContents
    sh1.Range("A1").Select

 

[Michael M]

Case "Yes"
for each cell in Range("H2:H" & cells(rows.count,"H").end(xlup).row)
cell.value = cell.value * 2204.6244202
next cell

 

[Edgarvelez]

Case "Yes"
for each cell in Range("H2:H" & cells(rows.count,"H").end(xlup).row)
cell.value = cell.value * 2204.6244202
next cell

Hi,

Added the code as follows:

    Case "Metric Ton|GW|Single Line|Single Line Weight"


    sh2.Cells(Rows.Count, 8).End(xlUp)(2).Resize(sh1.Range("D30").Value).NumberFormat = "0.000"
    sh1.Range("D26").Copy
    sh2.Cells(Rows.Count, 8).End(xlUp)(2).Resize(sh1.Range("D30").Value).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    Select Case sh1.Range("D10").Value
    Case "Yes"
    For Each cell In Range("H2:H" & Cells(Rows.Count, "H").End(xlUp).Row)
    cell.Value = cell.Value * 2204.6244202
    Next cell
    End Select
    sh1.Range("A1").Select
    sh1.Range("D30:D33").Select
    Selection.ClearContents
    sh1.Range("A1").Select

No errors but it did not change the values. Do you think we are missing Sh2. somewhere in the code?
I think we are close though.

 

[Edgarvelez]

Anybody else with some suggestions on this, Help!!!

 

[Edgarvelez]

I just noticed that it works but the results are in sh1. and need this to happen in sh2.
Any help anybody.

 

[Fluff]

How about

Case "Yes"
   With sh2.Range("H2", sh2.Range("H" & Rows.Count).End(xlUp))
      .Value = sh2.Evaluate("if(" & .Address & "="""",""""," & .Address & "*2204.62442)")
   End With
End Select

 

[Edgarvelez]

Fluff,

    sh2.Cells(Rows.Count, 8).End(xlUp)(2).Resize(sh1.Range("D30").Value).NumberFormat = "0.000"

Can I add * 2204.62442 to the end of this line and make this line like this

    sh2.Cells(Rows.Count, 8).End(xlUp)(2).Resize(sh1.Range("D30").Value).* 2204.62442

 

[Fluff]

No you cant.

 

[Edgarvelez]

Ok I get it, here's the problem.
I keep adding to the range as I keep using the macro and this line gets me to the exact part of the range.
Your code worked to a point but let me show you the results.

 

[Fluff]

What is the value in D30, also did you try the code I suggested?