iterate through a row to find a match

[[email protected]]

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!

[Carim]

HI,

Looks fine to me ...

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

HTH
Carim

[Bob Phillips]

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)

<[email protected]> wrote in message
news:[email protected]...
> 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!
>

[[email protected]]

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...

[[email protected]]

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