Variable range in VB

[famdamly]

I try to avoid asking questions here, mostly I learn from just recording macros then tinkering with them (that is just too cool), or the so called help feature. The times I have asked though I have been just blown away by the knowledge and generosity of the resident experts.

I hope I can explain this clearly,

I'm trying to select a variable range in VB based on cell data. I've tried the index function, it doesn't work for me. I thought that was the answer to my prayers.lol
Basically what I'm trying to do is have a macro that when executed takes values that were just entered into cells by a user and puts those values into a specific cell designated in the macro with a set column and a variable row depending on a value in a cell defined by a spinner or other means .

Essentially what I'm trying to do is use my custom oversized forms I've created in worksheets to enter data. Then I want to have that data compiled in other worksheets in plain old lists. Then I have my custom oversized reports that utilize the stored data in various ways.

There must be a simple macro floating around that accomplishes this seemingly everyday basic need of any business.

I have Access too but I haven't messed around with it too much because I'm really attached to the screens I've made in Excel for operating my business(because of my A.D.D lol). I didn't think Access offered that flexibility. Excel seems like it should be able to do anything I need it to, if I could just figure it out. I really don't mind the extra work. I'm just learning as I go and know very little really. It is really frustrating when you hit a stumbling block. This is a major hurdle for me. Can someone please enlighten me?


signed, Perplexed

[Tom Ogilvy]

Assume you have a sheet2 with headers in row 1 like

LastName FirstName CustID Amount

in columns A, B, C, D respectively.

On sheet1, you enter

B9 LastName
B10 FirstName
C5 CustID
C21 Amount

you could have a button on the Sheet1 (the oversized form) that when clicked
would post that information to the second sheet.

Private Sub Commandbutton1_Click()
set rng = worksheets("Sheet2").Cells(rows.count,1).End(xlup)(2)
With Worksheets("Sheet1")
rng.Value = .Range("B9")
rng.offset(0,1).Value = .Range("B10")
rng.offset(0,2).Value = .Range("C5")
rng.Offset(0,3).Value = .Range(C21")
.Range("B9:B10,C5,C21").clearcontents
End With
End Sub

This is a general example. I have no idea how the spinner would play into
the picture and you have not provided any details.

--
Regards,
Tom Ogilvy

"famdamly" <[email protected]> wrote in
message news:[email protected]...
>
> I try to avoid asking questions here, mostly I learn from just recording
> macros then tinkering with them (that is just too cool), or the so
> called help feature. The times I have asked though I have been just
> blown away by the knowledge and generosity of the resident experts.
>
> I hope I can explain this clearly,
>
> I'm trying to select a variable range in VB based on cell data. I've
> tried the index function, it doesn't work for me. I thought that was
> the answer to my prayers.lol
> Basically what I'm trying to do is have a macro that when executed
> takes values that were just entered into cells by a user and puts those
> values into a specific cell designated in the macro with a set column
> and a variable row depending on a value in a cell defined by a spinner
> or other means .
>
> Essentially what I'm trying to do is use my custom oversized forms
> I've created in worksheets to enter data. Then I want to have that data
> compiled in other worksheets in plain old lists. Then I have my custom
> oversized reports that utilize the stored data in various ways.
>
> There must be a simple macro floating around that accomplishes this
> seemingly everyday basic need of any business.
>
> I have Access too but I haven't messed around with it too much because
> I'm really attached to the screens I've made in Excel for operating my
> business(because of my A.D.D lol). I didn't think Access offered that
> flexibility. Excel seems like it should be able to do anything I need
> it to, if I could just figure it out. I really don't mind the extra
> work. I'm just learning as I go and know very little really. It is
> really frustrating when you hit a stumbling block. This is a major
> hurdle for me. Can someone please enlighten me?
>
>
> signed, Perplexed
>
>
> --
> famdamly
> ------------------------------------------------------------------------
> famdamly's Profile:
http://www.excelforum.com/member.php...o&userid=29382
> View this thread: http://www.excelforum.com/showthread...hreadid=503747
>

[famdamly]

That must have been simple for you. I'm amazed.

I've been trying to get those results for hours upon hours it seems. Wow

I should have just taken the Excel class they offer at the college, probably save myself some grief.

Thanks so much.