Creating an If/Then macro?
 

Hi, new to excel macro programming and was wondering if someone had a
suggestion for how I might do this.

I have some CSVs that i'm trying to process through excel. Essentially I
want to run a macro that will insert a column in a specific location (not a
problem), then it will go down each row in the previous column to look at
each cell, and if it's empty, it leaves the new cell in the next column
blank. If it finds text, such as "Item 1", it will put a part number in the
next column, like "TDI19384".

I'm trying to give it 8-10 product names, and when it finds a match, it
throws the SKU in the next column. Any suggestions are appreciated

Pete

Creating an If/Then macro?
 

here is some code, i also have a lookup table on sheet 2 in column c1 thru
d5, which is below


ID Number
item 1 TD19384
item 2 TD19385
item 3 TD19386
item 4 TD19386



Option Explicit
Dim rng As Range
Sub test()

Set rng = Range(Cells(65536, 1), Cells(1, 1).End(xlUp))
rng.Offset(0, 1).Formula =
"=IF(ISNA(VLOOKUP(A1,Sheet2!$C$1:$D$5,2,FALSE)),"" ""," & _
"VLOOKUP(A1,Sheet2!$C$1:$D$5,2,FALSE))"

End Sub



--


Gary


"PeteB" wrote in message
...
Hi, new to excel macro programming and was wondering if someone had a
suggestion for how I might do this.

I have some CSVs that i'm trying to process through excel. Essentially I
want to run a macro that will insert a column in a specific location (not
a problem), then it will go down each row in the previous column to look
at each cell, and if it's empty, it leaves the new cell in the next column
blank. If it finds text, such as "Item 1", it will put a part number in
the next column, like "TDI19384".

I'm trying to give it 8-10 product names, and when it finds a match, it
throws the SKU in the next column. Any suggestions are appreciated

Pete

Creating an If/Then macro?
 

i modified the code a little

Option Explicit
Dim rng As Range
Sub test()
Application.ScreenUpdating = False
Set rng = Range(Cells(65536, 1).End(xlUp), Cells(1, 1).End(xlUp))
rng.Offset(0, 1).Formula =
"=IF(ISNA(VLOOKUP(A1,Sheet2!parts,2,FALSE)),"" ""," & _
"VLOOKUP(A1,Sheet2!parts,2,FALSE))"
Application.ScreenUpdating = True
End Sub
--


Gary


"Gary Keramidas" wrote in message
...
here is some code, i also have a lookup table on sheet 2 in column c1 thru
d5, which is below


ID Number
item 1 TD19384
item 2 TD19385
item 3 TD19386
item 4 TD19386



Option Explicit
Dim rng As Range
Sub test()

Set rng = Range(Cells(65536, 1), Cells(1, 1).End(xlUp))
rng.Offset(0, 1).Formula =
"=IF(ISNA(VLOOKUP(A1,Sheet2!$C$1:$D$5,2,FALSE)),"" ""," & _
"VLOOKUP(A1,Sheet2!$C$1:$D$5,2,FALSE))"

End Sub



--


Gary


"PeteB" wrote in message
...
Hi, new to excel macro programming and was wondering if someone had a
suggestion for how I might do this.

I have some CSVs that i'm trying to process through excel. Essentially I
want to run a macro that will insert a column in a specific location (not
a problem), then it will go down each row in the previous column to look
at each cell, and if it's empty, it leaves the new cell in the next
column blank. If it finds text, such as "Item 1", it will put a part
number in the next column, like "TDI19384".

I'm trying to give it 8-10 product names, and when it finds a match, it
throws the SKU in the next column. Any suggestions are appreciated

Pete

Creating an If/Then macro?
 

Hi Gary,

Set rng = Range(Cells(65536, 1).End(xlUp), Cells(1, 1).End(xlUp))

I think that you intended:

Set rng = Range(Cells(65536, 1).End(xlUp), Cells(1, 1))

---
Regards,
Norman



"Gary Keramidas" wrote in message
...
i modified the code a little

Option Explicit
Dim rng As Range
Sub test()
Application.ScreenUpdating = False
Set rng = Range(Cells(65536, 1).End(xlUp), Cells(1, 1).End(xlUp))
rng.Offset(0, 1).Formula =
"=IF(ISNA(VLOOKUP(A1,Sheet2!parts,2,FALSE)),"" ""," & _
"VLOOKUP(A1,Sheet2!parts,2,FALSE))"
Application.ScreenUpdating = True
End Sub
--


Gary


"Gary Keramidas" wrote in message
...
here is some code, i also have a lookup table on sheet 2 in column c1
thru d5, which is below


ID Number
item 1 TD19384
item 2 TD19385
item 3 TD19386
item 4 TD19386



Option Explicit
Dim rng As Range
Sub test()

Set rng = Range(Cells(65536, 1), Cells(1, 1).End(xlUp))
rng.Offset(0, 1).Formula =
"=IF(ISNA(VLOOKUP(A1,Sheet2!$C$1:$D$5,2,FALSE)),"" ""," & _
"VLOOKUP(A1,Sheet2!$C$1:$D$5,2,FALSE))"

End Sub



--


Gary


"PeteB" wrote in message
...
Hi, new to excel macro programming and was wondering if someone had a
suggestion for how I might do this.

I have some CSVs that i'm trying to process through excel. Essentially
I want to run a macro that will insert a column in a specific location
(not a problem), then it will go down each row in the previous column to
look at each cell, and if it's empty, it leaves the new cell in the next
column blank. If it finds text, such as "Item 1", it will put a part
number in the next column, like "TDI19384".

I'm trying to give it 8-10 product names, and when it finds a match, it
throws the SKU in the next column. Any suggestions are appreciated

Pete

Creating an If/Then macro?
 

and another iteration that will leave blanks where there is no item number,
using an if and next statement

Option Explicit
Dim rng As Range
Dim lastrow As Long
Dim i As Long
Sub test()
Application.ScreenUpdating = True
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
i = 1
For i = 1 To lastrow
If Cells(i, "A").Value = "" Then
Cells(i, "A").Offset(0, 1).Formula = ""

Else

Cells(i, "A").Offset(0, 1).Formula = "=IF(ISNA(VLOOKUP(A" & i &
",Sheet2!parts,2,FALSE)),""""," & _
"VLOOKUP(A" & i & ",Sheet2!parts,2,FALSE))"

End If
Next i

Application.ScreenUpdating = True

End Sub


--


Gary


"PeteB" wrote in message
...
Hi, new to excel macro programming and was wondering if someone had a
suggestion for how I might do this.

I have some CSVs that i'm trying to process through excel. Essentially I
want to run a macro that will insert a column in a specific location (not
a problem), then it will go down each row in the previous column to look
at each cell, and if it's empty, it leaves the new cell in the next column
blank. If it finds text, such as "Item 1", it will put a part number in
the next column, like "TDI19384".

I'm trying to give it 8-10 product names, and when it finds a match, it
throws the SKU in the next column. Any suggestions are appreciated

Pete

Creating an If/Then macro?
 

probably, thanks

--


Gary


"Norman Jones" wrote in message
...
Hi Gary,

Set rng = Range(Cells(65536, 1).End(xlUp), Cells(1, 1).End(xlUp))

I think that you intended:

Set rng = Range(Cells(65536, 1).End(xlUp), Cells(1, 1))

---
Regards,
Norman



"Gary Keramidas" wrote in message
...
i modified the code a little

Option Explicit
Dim rng As Range
Sub test()
Application.ScreenUpdating = False
Set rng = Range(Cells(65536, 1).End(xlUp), Cells(1, 1).End(xlUp))
rng.Offset(0, 1).Formula =
"=IF(ISNA(VLOOKUP(A1,Sheet2!parts,2,FALSE)),"" ""," & _
"VLOOKUP(A1,Sheet2!parts,2,FALSE))"
Application.ScreenUpdating = True
End Sub
--


Gary


"Gary Keramidas" wrote in message
...
here is some code, i also have a lookup table on sheet 2 in column c1
thru d5, which is below


ID Number
item 1 TD19384
item 2 TD19385
item 3 TD19386
item 4 TD19386



Option Explicit
Dim rng As Range
Sub test()

Set rng = Range(Cells(65536, 1), Cells(1, 1).End(xlUp))
rng.Offset(0, 1).Formula =
"=IF(ISNA(VLOOKUP(A1,Sheet2!$C$1:$D$5,2,FALSE)),"" ""," & _
"VLOOKUP(A1,Sheet2!$C$1:$D$5,2,FALSE))"

End Sub



--


Gary


"PeteB" wrote in message
...
Hi, new to excel macro programming and was wondering if someone had a
suggestion for how I might do this.

I have some CSVs that i'm trying to process through excel. Essentially
I want to run a macro that will insert a column in a specific location
(not a problem), then it will go down each row in the previous column
to look at each cell, and if it's empty, it leaves the new cell in the
next column blank. If it finds text, such as "Item 1", it will put a
part number in the next column, like "TDI19384".

I'm trying to give it 8-10 product names, and when it finds a match, it
throws the SKU in the next column. Any suggestions are appreciated

Pete