Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
iterate through a row to find a match
never mind i got it to work!!!! yay!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find the second match using the match function | Excel Worksheet Functions | |||
Find an exact match and go to that match | Excel Discussion (Misc queries) | |||
Find partial match from column A,B and fill partial match in C? | Excel Discussion (Misc queries) | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
Can't iterate thru a row with For | Excel Programming |