[kevmakazi]

_____ A_____ B______ C

1 ___ 888___ Apple___ Blank
2 __________Orange__ 888
4 __________Orange__ 888
5 __________Orange__ 888
6 ___999____Apple____Blank
7 __________Orange __999
8 __________Orange__ 999
9 __________Orange__ 999



Please advise how to write a macro or VB in excel using above table.
The given table data contant in A and B column. The result is in C column where it search in B column for "Apple" will be blank and "Orange" will be the value of A1 and A7.

I have try Vlookup and IF then statement but no luck. I can not get the value on the left of "Apple" to display in C column.

Please advise,
Thanks

[Rollin_Again]

Can you upload a sample workbook showing exactly what you want. I am confused by your description.

Regards,
Rollin

[kevmakazi]

Originally Posted by Rollin_Again

Can you upload a sample workbook showing exactly what you want. I am confused by your description.

Regards,
Rollin

Please view the attached excel. Thank you so much for responed.


Best regards,
Kev

[Rollin_Again]

Will there ever be any blank rows in column B?

Regards,
Rollin

[kevmakazi]

Originally Posted by Rollin_Again

Will there ever be any blank rows in column B?

Regards,
Rollin

No, there is no blank rows in column B.

Only show blank rows in column C when "Apple" in column B is true.


Thanks,
Kev

[Rollin_Again]

Try the following macro. It assumes that the first row of data is on row 2.

Sub FillData()

For Each vCell In Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Cells

If UCase(vCell) = "APPLE" Then

Range(vCell.Address).Offset(0, 1).Value = ""

ElseIf UCase(vCell) = "ORANGE" Then

Range(vCell.Address).Offset(0, 1).Value = Range(vCell.Address).Offset(0, -1).End(xlUp).Value

End If

Next vCell

End Sub

Regards,
Rollin

[kevmakazi]

Originally Posted by Rollin_Again

Try the following macro. It assumes that the first row of data is on row 2.

Sub FillData()
 
For Each vCell In Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Cells
 
If UCase(vCell) = "APPLE" Then
 
Range(vCell.Address).Offset(0, 1).Value = ""
 
ElseIf UCase(vCell) = "ORANGE" Then
 
Range(vCell.Address).Offset(0, 1).Value = Range(vCell.Address).Offset(0, -1).End(xlUp).Value
 
End If
 
Next vCell
 
End Sub

Regards,
Rollin

Hi Rollin,

Thank you for your help. The macro work great.

May I ask you another favor. I know you use FOR LOOP to search for part # in column B. How would you code if Part# are filled in. In this case, the FOR LOOP would not work.

Please see attached file.

Thanks again,
Kev

[Rollin_Again]

You can use this code instead

Sub FillData()

For Each vCell In Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Cells

If UCase(vCell) = "APPLE" Then

vStartRow = vCell.Row

Range(vCell.Address).Offset(0, 1).Value = ""

ElseIf UCase(vCell) = "ORANGE" Then

Range(vCell.Address).Offset(0, 1).Value = Range("A" & vStartRow).Value
End If

Next vCell

End Sub

Regards,
Rollin

[kevmakazi]

You are the man! Its works great.

Regarding the code, could you explain what the codes below does:

1. vStartRow = vCell.Row

2. Range("A" & vStartRow).Value

Thanks again,
Kev

[Rollin_Again]

Originally Posted by kevmakazi

You are the man! Its works great.

Regarding the code, could you explain what the codes below does:

1. vStartRow = vCell.Row

2. Range("A" & vStartRow).Value

Thanks again,
Kev

I've created a loop that will check each cell in the entire range of cells. When the cell value is equal to "APPLE" you will set a variable (vStartRow) equal to the row number of that cell. Since you want to know that value of the cell in column A of the same row you then use Range("A" & vStartRow).Value to grab the value so that you can insert it into another cell(s) later in the code .

Regards,
Rollin

[kevmakazi]

Got it. I really do learn alots.
Thanks for your time.

Best regards,
Kev