Help writing a simple VBA loop formula please :)

[Estrella]

Hello

I don't think there is a "while" formula allowing me to do what I want so may need VBA help!

I want to make Excel understand that as long as the cells in column C are not empty, it needs to copy into cell A the value of the first non-blank cell C.

For example: there is "7000" in C1. I want "7000" copied across column A from cell A2 to cell A?? until cell C becomes empty. So if I have "7000" in cell C1, "8000" in C2 and then C3 blank C4 blank and data again in C5 I want in column A:
A1 blank, A2 "7000", A3 "7000", then blank in A4, A5 and A6 and A7 should equal C6 etc

Unfortunately the forum doesn't allow me to attach a file for illustration but I hope that makes sense.
I really can't find my way around with a formula, I guess I need to use a loop in VBA but no idea how to Any suggestions and help would be a life saver!

Many thanks
Estrella

 

[hiker95]

Estrella,

Welcome to the MrExcel forum.

What version of Excel are you using?

Can you post the raw data worksheet, and, on another worksheet post the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.


If you are not able to give us screenshots:
You can upload your workbook to Box Net,

sensitive data scrubbed/removed/changed

mark the workbook for sharing
and provide us with a link to your workbook.

 

[Estrella]

https://www.box.com/s/220feeea7e9aac1b38c0

Hello
Thank you for your reply. Above is the spreadsheet link on box.com
One sheet is the raw data the other sheet is the "wanted" data.

Estrella

 

[hiker95]

Estrella,

Thanks for the workbook.


Sample raw data:

Excel Workbook
	A	B	C
1		N/C:	7000
2		No	Type
3		837	JD
4
5
6		N/C:	7006
7		No	Type
8		839	JD
9
10
11		N/C:	7008
12		No	Type
13		446	BP
14
15
16		N/C:	7013
17		No	Type
18		848	JD
19
20
21		N/C:	7100
22		No	Type
23		481	BP
24		504	JD
25		695	JD
26		846	JD
27
raw data

After the macro, and AutoFilter is set to the used range in columns B and C:

Excel Workbook
	A	B	C
1		N/C:	7000
2	7000	No	Type
3	7000	837	JD
4
5
6		N/C:	7006
7	7006	No	Type
8	7006	839	JD
9
10
11		N/C:	7008
12	7008	No	Type
13	7008	446	BP
14
15
16		N/C:	7013
17	7013	No	Type
18	7013	848	JD
19
20
21		N/C:	7100
22	7100	No	Type
23	7100	481	BP
24	7100	504	JD
25	7100	695	JD
26	7100	846	JD
27
raw data

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 08/03/2012
' http://www.mrexcel.com/forum/showthread.php?651229-Help-writing-a-simple-VBA-loop-formula-please-)
Dim Area As Range, sr As Long, er As Long
Application.ScreenUpdating = False
For Each Area In Range("C1", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    sr = .Row
    er = sr + .Rows.Count - 1
    With Range("A" & sr + 1 & ":A" & er)
      .Value = Cells(sr, 3).Value
      .Font.Bold = True
    End With
  End With
Next Area
Range("B1:C" & er).AutoFilter
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.

 

[Estrella]

Thank you

I copied/pasted the macro without changing anything in the code but when I run it I get the following error:
"Run time error 1004: method 'autofilter' of object 'range' failed

Do you know what may cause it?
Many thanks
Estrella

 

[hiker95]

Estrella,

Your original raw data worksheet did not have AutoFilter set, but, the resulting worksheet did.

Try:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ReorgDataV2()
' hiker95, 08/03/2012
' http://www.mrexcel.com/forum/showthread.php?651229-Help-writing-a-simple-VBA-loop-formula-please-)
Dim Area As Range, sr As Long, er As Long
Application.ScreenUpdating = False
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
For Each Area In Range("C1", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    sr = .Row
    er = sr + .Rows.Count - 1
    With Range("A" & sr + 1 & ":A" & er)
      .Value = Cells(sr, 3).Value
      .Font.Bold = True
    End With
  End With
Next Area
Range("B1:C" & er).AutoFilter
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgDataV2 macro.

 

[Estrella]

That's great, it works even though I still have the error message but I get the result needed.
Many thanks for your help! I don't understand half of your code I will work on improving my skills, thank you again in the meaning time!