| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
While-Wend loop to fill in nulls
I'm trying to find any non-null cells in column f and then fill in any
null cell(s) below it with the value of that particular non-null cell until it gets to the next non-null cell in column f. And, then repeat this process for every non-null cell it until it gets to the end of my overall data. I've created the code shown below, but it only partially fills in the data; it only fills in one null cell below the non-null cell. I'm sure I'm missing something obvious. Dim h As Long For h = Cells(1, "g").End(xlDown).Row To 1 Step -1 If Cells(h, "f") < "" Then While Cells(h + 1, "f") = "" And Cells(h + 1, "g") < "" Cells(h + 1, "f") = Cells(h, "f") Wend End If Next h Some sample data is as follows: column f column g xxx-xx-xxxx INCENTIVE PAYMENTS YTD EXCLUDED FROM FICA-HI YTD yyy-yy-yyyy INCENTIVE PAYMENTS YTD EXCLUDED FROM FICA-HI YTD zzz-zz-zzzz SOLTAX EARNINGS NO 3 TAXABLE FICA-SS YTD TAXABLE FICA-HI YTD TAX FEDERAL WITHHOLDING TAX FICA-SS TAX FICA-HI SOLTAX DEDUCTION NO 1 999-99-999 SUPPL PAY |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
While-Wend loop to fill in nulls
Easier than you thought. Start from the top:
Sub marlie() h = Cells(Rows.Count, "G").End(xlUp).Row For i = 2 To h Set r = Cells(i, "F") If IsEmpty(r.Value) Then r.FillDown End If Next End Sub -- Gary''s Student - gsnu2007 "tbmarlie" wrote: I'm trying to find any non-null cells in column f and then fill in any null cell(s) below it with the value of that particular non-null cell until it gets to the next non-null cell in column f. And, then repeat this process for every non-null cell it until it gets to the end of my overall data. I've created the code shown below, but it only partially fills in the data; it only fills in one null cell below the non-null cell. I'm sure I'm missing something obvious. Dim h As Long For h = Cells(1, "g").End(xlDown).Row To 1 Step -1 If Cells(h, "f") < "" Then While Cells(h + 1, "f") = "" And Cells(h + 1, "g") < "" Cells(h + 1, "f") = Cells(h, "f") Wend End If Next h Some sample data is as follows: column f column g xxx-xx-xxxx INCENTIVE PAYMENTS YTD EXCLUDED FROM FICA-HI YTD yyy-yy-yyyy INCENTIVE PAYMENTS YTD EXCLUDED FROM FICA-HI YTD zzz-zz-zzzz SOLTAX EARNINGS NO 3 TAXABLE FICA-SS YTD TAXABLE FICA-HI YTD TAX FEDERAL WITHHOLDING TAX FICA-SS TAX FICA-HI SOLTAX DEDUCTION NO 1 999-99-999 SUPPL PAY |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
While-Wend loop to fill in nulls
Debra Dalgleish shares a couple of ways to do this:
http://contextures.com/xlDataEntry02.html Personally, unless this is a portion of a larger mechanized routine, I'd do it manually. I think it's quicker to do than to find the code, modify it for a specific column and run it. tbmarlie wrote: I'm trying to find any non-null cells in column f and then fill in any null cell(s) below it with the value of that particular non-null cell until it gets to the next non-null cell in column f. And, then repeat this process for every non-null cell it until it gets to the end of my overall data. I've created the code shown below, but it only partially fills in the data; it only fills in one null cell below the non-null cell. I'm sure I'm missing something obvious. Dim h As Long For h = Cells(1, "g").End(xlDown).Row To 1 Step -1 If Cells(h, "f") < "" Then While Cells(h + 1, "f") = "" And Cells(h + 1, "g") < "" Cells(h + 1, "f") = Cells(h, "f") Wend End If Next h Some sample data is as follows: column f column g xxx-xx-xxxx INCENTIVE PAYMENTS YTD EXCLUDED FROM FICA-HI YTD yyy-yy-yyyy INCENTIVE PAYMENTS YTD EXCLUDED FROM FICA-HI YTD zzz-zz-zzzz SOLTAX EARNINGS NO 3 TAXABLE FICA-SS YTD TAXABLE FICA-HI YTD TAX FEDERAL WITHHOLDING TAX FICA-SS TAX FICA-HI SOLTAX DEDUCTION NO 1 999-99-999 SUPPL PAY -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
While-Wend loop to fill in nulls
On Oct 11, 8:42 pm, Dave Peterson wrote:
Debra Dalgleish shares a couple of ways to do this:http://contextures.com/xlDataEntry02.html Personally, unless this is a portion of a larger mechanized routine, I'd do it manually. I think it's quicker to do than to find the code, modify it for a specific column and run it. tbmarlie wrote: I'm trying to find any non-null cells in column f and then fill in any null cell(s) below it with the value of that particular non-null cell until it gets to the next non-null cell in column f. And, then repeat this process for every non-null cell it until it gets to the end of my overall data. I've created the code shown below, but it only partially fills in the data; it only fills in one null cell below the non-null cell. I'm sure I'm missing something obvious. Dim h As Long For h = Cells(1, "g").End(xlDown).Row To 1 Step -1 If Cells(h, "f") < "" Then While Cells(h + 1, "f") = "" And Cells(h + 1, "g") < "" Cells(h + 1, "f") = Cells(h, "f") Wend End If Next h Some sample data is as follows: column f column g xxx-xx-xxxx INCENTIVE PAYMENTS YTD EXCLUDED FROM FICA-HI YTD yyy-yy-yyyy INCENTIVE PAYMENTS YTD EXCLUDED FROM FICA-HI YTD zzz-zz-zzzz SOLTAX EARNINGS NO 3 TAXABLE FICA-SS YTD TAXABLE FICA-HI YTD TAX FEDERAL WITHHOLDING TAX FICA-SS TAX FICA-HI SOLTAX DEDUCTION NO 1 999-99-999 SUPPL PAY -- Dave Peterson I created an add-in that has a Fill Blanks routine. The first thing it does is popup an input box asking for the column letter that is then assigned to a variable and used throughout the code. It cycles through that column until the last row of the spreadsheet is reached. Of course, there may be times when it doesn't need to cycle through to the last used row, but those times are so few and far between that I found it more effective to just write it for the most common process. I wrote it as a sub with a colLetter argument so that I can simply call the sub whenever needed using the column letter as it's argument such as fillBlanks "A". If this is something that you see yourself routinely doing I strongly urge that you do the same. As Dave said, it is very inefficient to have to modify the code constantly for different scenarios. Regards -Jeff- |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
While-Wend loop to fill in nulls
On Oct 11, 5:59 pm, JW wrote:
On Oct 11, 8:42 pm, Dave Peterson wrote: Debra Dalgleish shares a couple of ways to do this:http://contextures.com/xlDataEntry02.html Personally, unless this is a portion of a larger mechanized routine, I'd do it manually. I think it's quicker to do than to find the code, modify it for a specific column and run it. tbmarlie wrote: I'm trying to find any non-null cells in column f and then fill in any null cell(s) below it with the value of that particular non-null cell until it gets to the next non-null cell in column f. And, then repeat this process for every non-null cell it until it gets to the end of my overall data. I've created the code shown below, but it only partially fills in the data; it only fills in one null cell below the non-null cell. I'm sure I'm missing something obvious. Dim h As Long For h = Cells(1, "g").End(xlDown).Row To 1 Step -1 If Cells(h, "f") < "" Then While Cells(h + 1, "f") = "" And Cells(h + 1, "g") < "" Cells(h + 1, "f") = Cells(h, "f") Wend End If Next h Some sample data is as follows: column f column g xxx-xx-xxxx INCENTIVE PAYMENTS YTD EXCLUDED FROM FICA-HI YTD yyy-yy-yyyy INCENTIVE PAYMENTS YTD EXCLUDED FROM FICA-HI YTD zzz-zz-zzzz SOLTAX EARNINGS NO 3 TAXABLE FICA-SS YTD TAXABLE FICA-HI YTD TAX FEDERAL WITHHOLDING TAX FICA-SS TAX FICA-HI SOLTAX DEDUCTION NO 1 999-99-999 SUPPL PAY -- Dave Peterson I created an add-in that has a Fill Blanks routine. The first thing it does is popup an input box asking for the column letter that is then assigned to a variable and used throughout the code. It cycles through that column until the last row of the spreadsheet is reached. Of course, there may be times when it doesn't need to cycle through to the last used row, but those times are so few and far between that I found it more effective to just write it for the most common process. I wrote it as a sub with a colLetter argument so that I can simply call the sub whenever needed using the column letter as it's argument such as fillBlanks "A". If this is something that you see yourself routinely doing I strongly urge that you do the same. As Dave said, it is very inefficient to have to modify the code constantly for different scenarios. Regards -Jeff-- Hide quoted text - - Show quoted text - Thanks G.Student. That's what I needed Dave/Jeff: Thanks for the input. I needed this code as part of a larger routine. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| WEND in VBA | Excel Discussion (Misc queries) | |||
| Loop and fill Question | Excel Programming | |||
| Setting up a simple loop to fill labels? | Excel Programming | |||
| While ... Wend | Excel Programming | |||
| help on insert rows and fill down loop until end | Excel Programming | |||
Linear Mode
