Simplify Process with Excel 2003
 

Hi,

I am using Excel 2003 to update my website with products from my suppliers
website. So far, if I keep going at the current rate, it'll take forever.
Could someone find a way, or a macro that would speed this process up for me?
:

Process - I copy a list of products and their product numbers from supplier
website, paste into excel using unicode text in special paste function,
select all values in column "A", use drag function to move values down 1 row
to match values in column "B", Highlight all pasted items, press F5 for the
"go to" menu, select "special", then "blanks", go to Edit, delete, entire Row
(to remove all blank rows), select all of column "B", click "sort ascending"
function, select "expand the selection", click sort. Then I compare the items
in this spreadsheet with a complete list of items in another, if value of
column "B" (the product name) are the same (sometimes the first spreadsheet
is missing a few items), then I copy the items' corresponding item number in
column "A" onto the other spreadsheet with the complete list of items. Then
I manually fill in any missing item numbers.

This whole process is essentially for synchronizing the item numbers with
the same item numbers of the supplier. I guess a simple solution would be to
generate my own item numbers, but if I did that, it would make the process of
importing all the product images ALOT harder. So if anyone could PLEASE help
simplify most, if not all of this with some kind of macro, or simpler
process, I'd greatly appreciate it.

Much Thanks
--
„¢Â»ZrÃ¥xïûš«

Thanks guys, I found the answer. Here's the macro code if anyone else has a
similar problem. :


Sub DoProcess()
TrimList
CleanCopy
CheckNumbers
CheckProducts
AllocateNos

End Sub

Sub AllocateNos()

Dim LastRow As Integer
Dim ProdRow()
Dim Prices()
Dim NewProd As Boolean

NewProd = False
LastRow = Range("B65536").End(xlUp).Row()
ReDim ProdRow(LastRow, 2)
ReDim Prices(LastRow, 3)

'Read in list of Found Product rows
For i = 0 To LastRow - 1
If IsNumeric(Cells(i + 1, 7).Value) Then
ProdRow(i, 1) = Cells(i + 1, 7).Value
ProdRow(i, 2) = Cells(i + 1, 1).Value
Prices(i, 1) = Cells(i + 1, 7).Value
Prices(i, 2) = Cells(i + 1, 4).Value
Prices(i, 3) = Cells(i + 1, 5).Value

Else
ProdRow(i, 1) = "NotFound"
ProdRow(i, 2) = Cells(i + 1, 2).Value
Prices(i, 2) = Cells(i + 1, 4).Value
Prices(i, 3) = Cells(i + 1, 5).Value
End If
Next

'Insert data from array into Sheet1
Sheets("Sheet1").Select
Range("B65536").End(xlUp).Select
For i = 0 To LastRow - 1
If ProdRow(i, 1) = "NotFound" Then
With Range("B65536").End(xlUp)
.Offset(1, 0) = ProdRow(i, 2)
.Offset(1, 4) = Prices(i, 2)
.Offset(1, 5) = Prices(i, 3)
End With
NewProd = True
Else
Cells(ProdRow(i, 1), 1) = ProdRow(i, 2)

End If
Next
If NewProd = True Then
Sheets("Sheet2").Select
CheckNumbers
AllocateNos
End If
End Sub

Sub CleanCopy()
Dim LastRow As Integer

Application.ScreenUpdating = False
LastRow = Range("B65536").End(xlUp).Row()

Range("A1").Select
Selection.Insert Shift:=xlDown
For i = LastRow To 1 Step -1
If Application.WorksheetFunction.CountBlank(Rows(i)) = 256 Then
Rows(i).EntireRow.Delete
End If
Next

For Each cel In Intersect(Range("B:B"), ActiveSheet.UsedRange)
cel.Formula = Trim(cel)
Next

ActiveSheet.UsedRange.Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.ScreenUpdating = True
Range("A1").Select
End Sub

Sub CheckNumbers()
Dim LastRow As Integer
LastRow = Range("B65536").End(xlUp).Row()
Range("F1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=MATCH(RC[-5],Sheet1!C[-5],0)"
Range("F1").Select
Selection.AutoFill Destination:=Range(Cells(1, 6), Cells(LastRow, 6))
End Sub

Sub CheckProducts()
Dim LastRow As Integer
LastRow = Range("B65536").End(xlUp).Row()

Range("G1").Select
ActiveCell.FormulaR1C1 = "=MATCH(RC[-5],Sheet1!C[-5],0)"
Selection.AutoFill Destination:=Range(Cells(1, 7), Cells(LastRow, 7))

End Sub

Sub TrimList()
Dim cel
Sheets("sheet1").Select
For Each cel In Intersect(Range("B:B"), ActiveSheet.UsedRange)
cel.Formula = Trim(cel)
Next
Sheets("sheet2").Select
End Sub


"Zraxius" wrote:

Hi,

I am using Excel 2003 to update my website with products from my suppliers
website. So far, if I keep going at the current rate, it'll take forever.
Could someone find a way, or a macro that would speed this process up for me?
:

Process - I copy a list of products and their product numbers from supplier
website, paste into excel using unicode text in special paste function,
select all values in column "A", use drag function to move values down 1 row
to match values in column "B", Highlight all pasted items, press F5 for the
"go to" menu, select "special", then "blanks", go to Edit, delete, entire Row
(to remove all blank rows), select all of column "B", click "sort ascending"
function, select "expand the selection", click sort. Then I compare the items
in this spreadsheet with a complete list of items in another, if value of
column "B" (the product name) are the same (sometimes the first spreadsheet
is missing a few items), then I copy the items' corresponding item number in
column "A" onto the other spreadsheet with the complete list of items. Then
I manually fill in any missing item numbers.

This whole process is essentially for synchronizing the item numbers with
the same item numbers of the supplier. I guess a simple solution would be to
generate my own item numbers, but if I did that, it would make the process of
importing all the product images ALOT harder. So if anyone could PLEASE help
simplify most, if not all of this with some kind of macro, or simpler
process, I'd greatly appreciate it.

Much Thanks
--
„¢Â»ZrÃ¥xïûš«