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

 

[Edgarvelez]

D30 and D31 have to be a value of 1 always.
Yes I did try you code and here are the results

 

[Fluff]

This line of code is looking at a single cell below the last used cell in col H (the resize is pointless if D30 is always 1)

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

Therefore multiplying that cell by 2204.6244202 will simply return 0, so not sure what you are trying to achieve

 

[Edgarvelez]

Sorry this will make better sense, its a similar line that I am using further down in the macro

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

I this case this range in row 8 or H, resized based on D24 is what I am looking to *2204.6244202
Keep in mind that to this range I keep adding lines (Dynamic) this is why I am using .End(xlUp)(2).Resize(sh1.Range("D24")

 

[Fluff]

But there is nothing in those cells.

 

[Edgarvelez]

But there is nothing in those cells.

There will be a values there in row 8, this piece of code will be run afterwards those values are there I guarantee that.

 

[Edgarvelez]

There will be a values there in row 8, this piece of code will be run afterwards those values are there I guarantee that.

There will be a values there in row 8, this piece of code will be run afterwards those values are there I guarantee that.

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

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

I changed the code around, this will take D26 value on to sh2. paste it as many times D24 calls for.
This is the same range that I am trying to capture for the formula of *2204.62442

 

[Fluff]

But after you have put those values in this sh2.Cells(Rows.Count, 8).End(xlUp)(2)will find the 1st blank cell below what you have just pasted.
you can multiply the numbers you've pasted at the same time quite easily, but that then negates your 2nd select case.

 

[Michael M]

It might be easier if you either
1. Use XL2BB to post your data here so we can play with actual setups
OR
2. Upload the file to a hosting site, dropbox for instance, then post a link to that file back here...my preferred option

 

[Edgarvelez]

But after you have put those values in this sh2.Cells(Rows.Count, 8).End(xlUp)(2)will find the 1st blank cell below what you have just pasted.
you can multiply the numbers you've pasted at the same time quite easily, but that then negates your 2nd sel

My data will come in in Metric Tons, I will enter it in Metric tons as you can see, however since we (USA) are the only ones in the planet that uses pounds, I some times have to convert it to Pounds (about 50% of the time this is done) and by selecting case YES I can convert it, that simple.
The formula for converting MT to LB is by multiplying (MT*2204.62442)

 

[Fluff]

Do you want to ignore the select case Yes & simply multiply all the pasted values?