Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop driver drivers error ethernet excel freeze gaming graphics hard drive hardware hdmi internet laptop malware memory monitor motherboard network printer problem ram registry repair router slow software sound svchost.exe trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Excel macro help

Reply  
Thread Tools
kevmakazi's Avatar
Junior Member with 6 posts.
 
Join Date: Jun 2008
23-Jun-2008, 08:05 PM #1
Excel macro help
_____ 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

Last edited by kevmakazi; 23-Jun-2008 at 08:15 PM..
Rollin_Again's Avatar
Senior Member with 4,273 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Brilliant When Sober
24-Jun-2008, 03:45 PM #2
Can you upload a sample workbook showing exactly what you want. I am confused by your description.

Regards,
Rollin
kevmakazi's Avatar
Junior Member with 6 posts.
 
Join Date: Jun 2008
24-Jun-2008, 06:01 PM #3
Quote:
Originally Posted by Rollin_Again View Post
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
Attached Files
File Type: xls Excel_macro.xls (25.0 KB, 119 views)

Last edited by kevmakazi; 24-Jun-2008 at 08:46 PM..
Rollin_Again's Avatar
Senior Member with 4,273 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Brilliant When Sober
24-Jun-2008, 06:32 PM #4
Will there ever be any blank rows in column B?

Regards,
Rollin
kevmakazi's Avatar
Junior Member with 6 posts.
 
Join Date: Jun 2008
24-Jun-2008, 06:55 PM #5
Quote:
Originally Posted by Rollin_Again View Post
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's Avatar
Senior Member with 4,273 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Brilliant When Sober
24-Jun-2008, 06:56 PM #6
Try the following macro. It assumes that the first row of data is on row 2.

Code:
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

Last edited by Rollin_Again; 24-Jun-2008 at 07:05 PM..
kevmakazi's Avatar
Junior Member with 6 posts.
 
Join Date: Jun 2008
24-Jun-2008, 07:54 PM #7
Quote:
Originally Posted by Rollin_Again View Post
Try the following macro. It assumes that the first row of data is on row 2.

Code:
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
Attached Files
File Type: xls Excel_macro.xls (25.0 KB, 117 views)

Last edited by kevmakazi; 24-Jun-2008 at 07:59 PM..
Rollin_Again's Avatar
Senior Member with 4,273 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Brilliant When Sober
24-Jun-2008, 09:29 PM #8
You can use this code instead

Code:
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's Avatar
Junior Member with 6 posts.
 
Join Date: Jun 2008
25-Jun-2008, 01:37 PM #9
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's Avatar
Senior Member with 4,273 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Brilliant When Sober
25-Jun-2008, 02:42 PM #10
Quote:
Originally Posted by kevmakazi View Post
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's Avatar
Junior Member with 6 posts.
 
Join Date: Jun 2008
25-Jun-2008, 07:15 PM #11
Got it. I really do learn alots.
Thanks for your time.

Best regards,
Kev
Reply

Tags
case, excel, if then, macro

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 07:04 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.