Help with a macro

[JasonLevan]

I am having a horrible time figuring out something i am sure is simple.

In column A I have 7500 item numbers, In column B I have there prices.

In column C I have NEW Items that just came in and column D has the prices for those numbers.

What i need is a macro to search column A for the matching item number in column C and find the current price of it in column B and post that current price in say column E. And have it do this for each new item so i can compare old and new prices.

Any help is appreciated.

 

[Brianwarnock]

You could achieve that with a Vlookup
something like this in each cell in E, this would be in E10

=Vlookup(c10,A$1:B$7500,2,False)


Brian

 

[JasonLevan]

here is my spreadsheet column a is item numbers column b is prices, column c is new items that came in. in column d i need it to lookup each item number in column c in column a and give me what its current price is for that item that came in. Hope this helps because i am frustrated.

thanks

 

[Brianwarnock]

You have changed the requirement but what I wrote is still applicable I wrote in cell D3
=VLOOKUP(C3,A$3:B$7500,2,FALSE)

and it picked up the answer from row 193, drag the formula down and it fills in col D but with some #N/A where there is no match.

try it

Brian

 

[JasonLevan]

thanks brian that works great, any idea how to make it leave a blank field rather than n/a

 

[Brianwarnock]

You can wrap it in an If to check

IF(ISNA(Vlookup.......)," ",Vlookup(.......))

Brian

 

[JasonLevan]

awsome, ok I got a new one for you. I have a workbook with many columns in it. I need a macro to find the first blank row in column c and then insert new blank row. Is that possible? Thanks

 

[Brianwarnock]

Yes it is possible but this requires code and not sure when I'll get to it.
It is best not to add questions to an existing thread but to start a new one,Is the Inserted row to be after the row containing the blank in col c

Brian

 

[JasonLevan]

that would be fine

 

[JasonLevan]

=if(isna(vlookup(c3,a$3:b$7500,2,false),"",vlookup(c3,a$3:b47500,2,false))

it says i entered to many arguments, where is my mistake

 

[Brianwarnock]

You didn't hold the shift key

=if(isna(vlookup(c3,a$3:b$7500,2,false),"",vlookup(c3,a$3:b47500,2,false))

I assume that there is not a space after the second VLookup, its a forum problem.

Brian

 

[Brianwarnock]

Here is your code.

Sub insertrow()
' BRian Warnock Jan 2011
' look for first Blank cell in column C and insert a row following this.

Dim lastrow As Long
Dim r As Long

lastrow = ActiveSheet.UsedRange.Rows.Count

For r = 1 To lastrow
If Cells(r, "C") = "" Or Cells(r, "C") = " " Then
Rows(r + 1).Insert (xlShiftDown)
Exit Sub
End If
Next r

MsgBox " NO Empty cells in Column C"

End Sub

 

[JasonLevan]

You didn't hold the shift key

=if(isna(vlookup(c3,a$3:b$7500,2,false),"",vlookup(c3,a$3:b47500,2,false))

I assume that there is not a space after the second VLookup, its a forum problem.

Brian

i fixed the 47500 problem but it stops at the "" in the middle

 

[Brianwarnock]

Typical bracketting problem, these syntax problems can be a pain
=if(isna(vlookup(c3,a$3:b$7500,2,false)),"",vlookup (c3,a$3:b$7500,2,false))

Brian

 

[JasonLevan]

Typical bracketting problem, these syntax problems can be a pain
=if(isna(vlookup(c3,a$3:b$7500,2,false)),"",vlookup (c3,a$3:b$7500,2,false))

Brian

works!!!!! thanks man

 

[JasonLevan]

Typical bracketting problem, these syntax problems can be a pain
=if(isna(vlookup(c3,a$3:b$7500,2,false)),"",vlookup (c3,a$3:b$7500,2,false))

Brian

works!!!!! thanks man maybe you can fix my other problems i posted