Add formula to dynamic range

Edgarvelez

Board Regular
Joined
Jun 6, 2019
Messages
197
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
    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
 

Attachments

  • Sheet 2A.JPG
    Sheet 2A.JPG
    80.9 KB · Views: 16
  • Sheet 2B.JPG
    Sheet 2B.JPG
    79.9 KB · Views: 15
Do you want to ignore the select case Yes & simply multiply all the pasted values?
Would pref to keep select case YES but I will take what ever you can provide it would make my life easier I convert this stuff all day long. No wonder the London Metals Exchange says stick to Metric Tons.. LOL
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Still don't really understand what you are after, but this is my best guess
VBA Code:
    Case "Metric Ton|GW|Single Line|Single Line Weight"
    Set NewRws = sh2.Cells(Rows.Count, 8).End(xlUp)(2).Resize(Sh1.Range("D24").Value)
    NewRws.NumberFormat = "0.000"
    Sh1.Range("D26").Copy
    NewRws.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    Select Case Sh1.Range("D10").Value
    Case "Yes"
      NewRws.Value = NewRws.Value * 2204.62442
    End Select
    Sh1.Range("A1").Select
    Sh1.Range("D30:D33").Select
    Selection.ClearContents
    Sh1.Range("A1").Select
 
Upvote 0
You did it again!! It worked awesome!!
Sorry for the back and forth explaining the end result that I was looking for, but in the end you figured it out..
Thank you very much.
 

Attachments

  • Awsome you did it again.JPG
    Awsome you did it again.JPG
    126.5 KB · Views: 6
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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
Back
Top