Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match function...random search? | Excel Worksheet Functions | |||
How to use a variable for a range | Excel Worksheet Functions | |||
Is it possible to set a variable range in a macro? | About this forum | |||
Set Data Range by Variable | Excel Discussion (Misc queries) | |||
variable range countif | Excel Worksheet Functions |