Dynamic Range In Vba using inputbox

Question

I am working on a vba project currently where i have to working on thermal expansion formulae.

Now I am almost done I just need help with little something.

Consider there are 2 inputbox with variable VarFirstAnchor and VarLastAnchor in sheet "ManualInput"

Dim VarFirstAnchor As Integer
VarFirstAnchor = Application.InputBox("Please enter initial Anchor Point", "Initial Anchor Point ", 1)
Range("A21").Value = "First Anchor Point"
Range("B21").Value = Int(VarFirstAnchor)

Dim VarLastAnchor As Integer
VarLastAnchor = Application.InputBox("Please enter Last Anchor Point", "Last Anchor Point ", 1)
Range("A23").Value = "Last Anchor Point"
Range("B23").Value = Int(VarLastAnchor)

And I have a userform that dynamically creates desired number of text box to enter floor height.

Now I have created another sheet called temporarySave which stores the every value that user enters in that userform textbox in "column A" so if user entered 20 floor heights then temporarySave sheet will be filled up to A20 with the heights user entered

Now let's say user entered 5 in VarFirstAnchor input box and 12 in VarLastAnchor input box, then I want sum of floor height from floor 5 to floor 10.

Thank you.

In your scenario floor one is always row one and floor 20 is row 20? — Warcupine
– Warcupine, Commented Feb 5, 2021 at 16:52
Yes its always like that, if user entered 5 values for floor height let's say its 116,120,139,128,170 then it would be filled as (A1) 116 (A2) 120 (A3) 139 (A4) 128 (A5) 170 — HalfSparrow
– HalfSparrow, Commented Feb 5, 2021 at 16:58

Warcupine · Accepted Answer · 2021-02-05 18:07:03Z

This should do what you are looking for. I put the sum in a MsgBox but you can do whatever with it.

I changed your Integers to Longs, it probably doesn't matter in this particular case but it's pretty easy to get overflow errors with integers in vba. If your floor heights can have decimals change the Long to Double.

I also changed your implicit sheet references to explicit ones.

    With Sheets("ManualInput")
        Dim VarFirstAnchor As Long
        VarFirstAnchor = Application.InputBox("Please enter initial Anchor Point", "Initial Anchor Point ", 1)
        .Range("A21").Value = "First Anchor Point"
        .Range("B21").Value = Int(VarFirstAnchor)
        
        Dim VarLastAnchor As Long
        VarLastAnchor = Application.InputBox("Please enter Last Anchor Point", "Last Anchor Point ", 1)
        .Range("A23").Value = "Last Anchor Point"
        .Range("B23").Value = Int(VarLastAnchor)
    End With
    
    Dim sumVal As Long
    With Sheets("temporarySave")
        sumVal = Application.Sum(.Range(.Cells(VarFirstAnchor, 1), .Cells(VarLastAnchor, 1)))
    End With
    
    MsgBox sumVal

Collectives™ on Stack Overflow

Dynamic Range In Vba using inputbox

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related