Hi All,
I’ve been trying to write a script and whilst I’ve “sort of” learned some of the components I’d need I’m struggling to get going. I have no previous experience in VBA and whilst have some minor successes I’m a greenhorn self teaching programmer.
I haven’t been able to find an example close to what I am trying to do.
Workbook details:
The worksheet is to manage a Ballot draw for a local model railway club. Some members will only enter the ballot for 1 – 10 items while others may enter for many.
I hope that makes sense. Sorry for the mishmash of attempted variables etc. None of my cells as yet have any ranges defined etc so if you need them or wish to change anything above for the sake of simplicity please feel free to do so.
Looking forward on a conversation that helps me understand the component parts needed for this script.
Regards
I’ve been trying to write a script and whilst I’ve “sort of” learned some of the components I’d need I’m struggling to get going. I have no previous experience in VBA and whilst have some minor successes I’m a greenhorn self teaching programmer.
I haven’t been able to find an example close to what I am trying to do.
Workbook details:
The worksheet is to manage a Ballot draw for a local model railway club. Some members will only enter the ballot for 1 – 10 items while others may enter for many.
- I have two sheets
- Sheet 1 has a header row and a blank row 2. Column A has the text “Lot 1” to “Lot 187” in cells A3:A189, column C is a formula random draw reading each row and columns E2 – AC189 will receive the data I need copied from Sheet 2
- Sheet 2 has a header row.. Column A is “Member Name”, Columns B – FZ hold text submitted from a form (eg “Lot 1 3003 Class 24 w/tender” or “Lot 107 VTG Cylinder wagon”. Currently the smallest entry is for just one item while the largest is for 155 lots!
- I can remove all Blanks from a row in sheet 2 using Selection.SpecialCells(xlBlanks).Delete shift:=xltoleft but as per above some rows have more cells populated than others.
NOTE! The Sheet1 Col A text is not matched exactly to the text from columns B – FZ in sheet 2. I’m assuming we can use LEFT([cellnum],6) but if the Lot is lot number 1 – 9 then the returned string potentially could be “Lot 1 3” as in “Lot 1 3003 Class 24 w/tender”. Is there a way instead search for a [space]. I could then REPLACE “Lot “ with “Lot_” thus allowing you to variably search out to the first space. That way you’d get just the Lot Number “Lot_1”, “Lot_21” or “Lot_121” from step 3a below
- I need the process to do the following …
- Start in Sheet 2 cell A2
- Record the NAME in a variable (MemName)
- If this returns a Blank Cell then you’ve reached the end of the list of submissions and can STOP
- If MemName returned is not blank then Move right into that row (CurrentRow) starting in cell B[CurrentRow] (ActionCell)
- Read the cell string an save the Lot number (LotString)
- Go to Sheet 1
- Search down Column A looking for a match to LotString (if you reach the a blank cell without stopping you’ve not found a match so I’m not sure how to handle that!)
- When a match is found move right into that row to cell E[rownum]
- Check for a blank cell, if not blank move right to next cell and continue to move right until you find a Blank Cell
- Write MemName into that Blank cell
- Increment ActionCell, then Return to Sheet 2 and go to cell [CurrentRow],[ActionCell]
- If a blank cell is found in ActionCell then you have reached the end of that users submitted lots, so drop to the next row down in Sheet2, Column A and start again from step 2.
I hope that makes sense. Sorry for the mishmash of attempted variables etc. None of my cells as yet have any ranges defined etc so if you need them or wish to change anything above for the sake of simplicity please feel free to do so.
Looking forward on a conversation that helps me understand the component parts needed for this script.
Regards
Last edited: