CODEWITHSUNDEEP
excelvba
HalfSparrow
HalfSparrow

Reputation: 27

Dynamic Range In Vba using inputbox

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.

Upvotes: 1

Views: 434

Answers (1)

Warcupine
Warcupine

Reputation: 4640

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

Upvotes: 2

Related Questions