VBA for column offset

RichP

Board Regular
Joined
Jul 31, 2002
Messages
63
Hi there,
wonder if you might be able to lend me a hand, please?
I am using a form with many text boxes and comboboxes to enter data into the right place in a table. After filling in the relevant boxes, click a button and the values are transferred into the right places on the table.
The code goes something like this:

'Name of Item 1 into col G
Range("G" & FstRow).Value = TextBox208.Value
'Volume of Item 1 into col H
Range("H" & FstRow).Value = TextBox209.Value
'Currency of Item 1 into col I
Range("I" & FstRow).Value = ComboBox184.Value
'Price of Item 1 into col J
Range("J" & FstRow).Value = TextBox211.Value

FstRow refers to the first empty row.

The above block of 4 elements represents one item and there are approximately 20 items in the table. We're looking at increasing the pieces of information per item to 5, possibly 7 in the future. This would involve inserting one or more columns into the table for each item. If I insert these rows, I would then have to edit every line to reflect the new column.
Is there a way - I'm sure there must be, I just cannot work it out - that I can define an offset value and use that instead of "hard" coding the column to make the information go to the right place?

Not sure if I'm explaining this properly, please ask if you need more info from me.
Many thanks,
Richard
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
To my mind, the best way is to search the table headings (presumably the first row of the sheet) for the appropriate column and then add the values in the correct locations with ws.cells(FstRow,Col).value=TxtBox.value. More work to setup, but once it's running, modifications are much easier. Col=ws.rows(1).find(ColHeading,lookin:=xlvalues,lookat:=xlwhole).Column. That just an example of the find statement. It really should be split into 2 steps to check for success of finding to prevent trying to get the column of a null range.
 
Upvote 0
The code goes something like this:

'Name of Item 1 into col G
Range("G" & FstRow).Value = TextBox208.Value
'Volume of Item 1 into col H
Range("H" & FstRow).Value = TextBox209.Value
'Currency of Item 1 into col I
Range("I" & FstRow).Value = ComboBox184.Value
'Price of Item 1 into col J
Range("J" & FstRow).Value = TextBox211.Value

FstRow refers to the first empty row.

The above block of 4 elements represents one item and there are approximately 20 items in the table. We're looking at increasing the pieces of information per item to 5, possibly 7 in the future.
You will always have to add something to your code to account for the additional TextBoxes and/or ComboBoxes that will contain the information for your new columns, but perhaps this will help you some. Define a variable of type Long (I'll call it NumOfCols for this posting) that you assign the number of columns in your items, then the following two lines of code can replace the 4 lines (excluding comment lines) of code you posted above...

Code:
NumOfCols = 4
Range("G" & FstRow).Resize(, NumOfCols) = Array(TextBox208.Value, TextBox209.Value, _
                                                ComboBox184.Value, TextBox211.Value)

That way, you can just add your new TextBox or ComboBox names to the comma delimited argument for the Array function.
 
Last edited:
Upvote 0
I've had a think about this, maybe the following would work?
If I write out the first item in full, as above, using the letters for the column reference, can I set a variable for the offset and use that in subsequent blocks? In this way the Name of Item 2 would be in Column (G + offset value), Row (FstRow), Volume into Column(H + offset value), etc.?
Many thanks,
Richard
 
Upvote 0
function LocateCol(ColHeading as string,R as range) as Long
dim C as range
set c=r.find(colheading,lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
Locatecol=0
'***ERROR*** This should not happen since you created the column headings yourself
else
LocateCol=c.column
endif
set c=nothing
end function

Use it by:

dim Txt1Col as long
dim Txt1ColHeading as string

Txt1ColHeading="Whatever it is"
txt1col=LocateCol(Txt1ColHeading,ws.rows(1))

Now, when you want to update:

ws.cells(Rw,Txt1Col).value=Txt1.value

If the column headings are not in the first row, just change it in the call to LocateCol
 
Upvote 0
You've got to love MrExcel.
Gents, thank you for such a quick reply. Ed, I'm still muddling through your solution, unfortunately my VBA skills were never fantastic, and they're currently even rustier than normal at the moment...
Rick, your solution is hugely effective, and remarkably easy to implement, so thank you very much indeed.

Until the next time I get totally stuck on something,
Richard
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top