Variable range in VB
 

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

Variable range in VB
 

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" wrote in
message ...

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

Variable range in VB
 

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.


--
famdamly
------------------------------------------------------------------------
famdamly's Profile: http://www.excelforum.com/member.php...o&userid=29382
View this thread: http://www.excelforum.com/showthread...hreadid=503747