iterate through a row to find a match
 

hi there, i'm trying have a table that lists prices for different
products from many stores. so, i've already made a macro to find the
min price found for each product but i want to write a macro that will
by iterate through the product's row (from left to right) until it
finds the first price that matches what i found as my minimum price and
then give me the name of that store that offers the product at that
price. This is what I have so far...

Sub ConstructCheapestStoresTable()
Dim productRow As Integer
Dim productNumber As Integer
Dim ShopName As String
Dim StoreColumn As Integer
Dim rowoffset As Integer
Dim productName As String

productRow = productNumber + 1
rowoffset = numProducts + 7
productNumber = 1
numProducts = 11

Range(Cells(2, 1), Cells(numProducts + 1, 1)).Select
'Range("R2C1:RnumProducts + 1C1").Select
Selection.Copy
Range(Cells(rowoffset + 1, 1), Cells(rowoffset + numProducts,
1)).Select
'Range("R" & CStr(rowoffset + 1) & "C1" & ":R" & CStr(rowoffset +
numProducts) & "C2").Select
ActiveSheet.Paste

Do While Worksheets("Table").Cells(productRow, 1) < ""

If Worksheets("Table").Cells(productRow, StoreColumn) = _
Worksheets("Table").Cells(productRow, numSites + 2) Then

productName = Worksheets("Table").Cells(productRow, 1).Value
'added
ShopName = Worksheets("Table").Cells(1, StoreColumn).Value
'added

Worksheets("Table").Cells(rowoffset + 1, 2).Value = productName
'added
Worksheets("Table").Cells(rowoffset + 1, 1).Value = ShopName 'added

End If

productRow = productRow + 1 'added

Loop

Worksheets("Table").Cells(rowoffset, 1).Value = "Product"
Worksheets("Table").Cells(rowoffset, 2).Value = "Cheapest Store"

Worksheets("Table").Select
Range(Cells(rowoffset, 1), Cells(rowoffset + numProducts,
2)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub


i'm having trouble getting vba to read what's inside my do while loop.
everything else works except the getting the store name!!

any help would be greatly appreciated. thanks!

iterate through a row to find a match
 

HI,

Looks fine to me ...

Try to Dim ShopName as Variant to see if it is a defintion problem ...

HTH
Carim


--
Carim
------------------------------------------------------------------------
Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
View this thread: http://www.excelforum.com/showthread...hreadid=533012

iterate through a row to find a match
 

Try using Application.Match to find it. As an example

Dim iPos As Long
iPos = 0
On Error Resume Next
iPos = Application.Match(myVal, Range("A1:M1"),0)
On Error Goto 0
If iPos < 0 Then
Msgbox "Found"
End If

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
oups.com...
hi there, i'm trying have a table that lists prices for different
products from many stores. so, i've already made a macro to find the
min price found for each product but i want to write a macro that will
by iterate through the product's row (from left to right) until it
finds the first price that matches what i found as my minimum price and
then give me the name of that store that offers the product at that
price. This is what I have so far...

Sub ConstructCheapestStoresTable()
Dim productRow As Integer
Dim productNumber As Integer
Dim ShopName As String
Dim StoreColumn As Integer
Dim rowoffset As Integer
Dim productName As String

productRow = productNumber + 1
rowoffset = numProducts + 7
productNumber = 1
numProducts = 11

Range(Cells(2, 1), Cells(numProducts + 1, 1)).Select
'Range("R2C1:RnumProducts + 1C1").Select
Selection.Copy
Range(Cells(rowoffset + 1, 1), Cells(rowoffset + numProducts,
1)).Select
'Range("R" & CStr(rowoffset + 1) & "C1" & ":R" & CStr(rowoffset +
numProducts) & "C2").Select
ActiveSheet.Paste

Do While Worksheets("Table").Cells(productRow, 1) < ""

If Worksheets("Table").Cells(productRow, StoreColumn) = _
Worksheets("Table").Cells(productRow, numSites + 2) Then

productName = Worksheets("Table").Cells(productRow, 1).Value
'added
ShopName = Worksheets("Table").Cells(1, StoreColumn).Value
'added

Worksheets("Table").Cells(rowoffset + 1, 2).Value = productName
'added
Worksheets("Table").Cells(rowoffset + 1, 1).Value = ShopName 'added

End If

productRow = productRow + 1 'added

Loop

Worksheets("Table").Cells(rowoffset, 1).Value = "Product"
Worksheets("Table").Cells(rowoffset, 2).Value = "Cheapest Store"

Worksheets("Table").Select
Range(Cells(rowoffset, 1), Cells(rowoffset + numProducts,
2)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub


i'm having trouble getting vba to read what's inside my do while loop.
everything else works except the getting the store name!!

any help would be greatly appreciated. thanks!

iterate through a row to find a match
 

would anyone know why i am getting a run time error 1004
(application-defined or object-defined error) at this line:

If Worksheets("Table").Cells(productRow, StoreColumn).Value = _
Worksheets("Table").Cells(productRow, numSites + 2).Value Then

i seems right to me...

iterate through a row to find a match
 

never mind i got it to work!!!! yay!