Vba code to populate next open alternate rows from userform

[sintek]

Hi All

I have submitted code that I have been trying to figure out for days with no luck.
I have had variations of results but still none that meets the required result.

My worksheets gets populated from a user form and I require the following:
If Option Cheque is chosen from ComboBox2 then the first green row must be populated i.e. Row 3
Thereafter each alternate green row must be populated i.e Row 5, 7, 9 etc
If Option Cash is chosen from ComboBox2 then the first yellow row must be populated i.e. Row 4
Thereafter each alternate yellow row must be populated i.e. Row 6, 8 ,10 etc

Take note: Row 50 is a row containing formulas for totals Row 3 - 49.

I thank you in advance for your expertise.

Please Login or Register  to view this content.

[pjwhitfield]

You say the 1st Green or Yellow row depending on the value and then give examples of the row numbers, are these set in stone as such ie are green rows the odd ones and yellow the evens?

if the first 5 entries are cheques then would you expect to see rows 3,5,7,9 and 11 populated? if the 6th is then Cash do you expect to populate 4 or 12? And Im going to be a pain and ask a newbie type question but is there a valid reason for these set rows? could you not just have an indicator that shows "Cheque" or "Cash" and then complete the rows as they happen? ie in the example above rows 3 - 7 would show Cheque and then 8 would contain the Cash item.

Ideally, if you can upload your workbook that would help immensely.

[sintek]

Hi PJ

Thank you for your response.
The green and yellow are set in stone, yes .i.e Green are odds and Yellow are evens.
If first 5 entries are Cheque then 3,5,7,9 & 11 will be populated - correct, If the next is a Cash transaction, then i will prefer Row 4 to be populated as there could be multiple cash transactions as well.
Valid reason: Formulas in each individual row that control Cash Float and Cheque Bank Balance which flows through to Trial Balance.

My apologies...My file does not want to upload. Strange as it is only 186kb

[pjwhitfield]

I think you can change it to the following, I see the Cheque and Cash question as simply a need to determine the starting row of the scan for a blank row therefore Ive removed the additional setting of the cells.

Please Login or Register  to view this content.

You may want to move the resetting of values into the IF statement, depending on how you want it to handle there being no blank row in the 1st 49

[sintek]

Hi PJ

Wow, an amazing improvement. There is however no differentiation with start row. i.e. Cheque or Cash starts in same row. and when i toggle between payments, it finds the last transaction and populates the nth row down.

[pjwhitfield]

I can only assume the words "Cheque" is not written exactly the same?

try changing:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

*note that Ive also changed the 1st letter of Cheque to lowercase* (an easy thing to miss but massive impact!)

[sintek]

Hi PJ

The words were written the same way, however, EUREKA, your new code change sorted it out.

Thanks for your expertise and lesson.

[sintek]

Hi PJ

The words were written the same way, however, EUREKA, your new code change sorted it out.

Thanks for your expertise and lesson.