Matching rows of data with another

[everscern]

Hi all,
this is the crux of my project. but i really am at wits end. pls help. I would very much appreciate.

i have 3 rows. one row is the variable.

column 1 = 1 2 3
column 2 = 1 2 3
column 3 = 4 5 6
with parameters 1,2 3 , column 2 will have a stock of 10.
with parameter 4,5,6 , column 3 will have a stock of 20.

now, comparing column 1, 2,3. column 1 and 2 is similar. Thus, column 1 must return a stock value of 10 too.

I need a macro for this. Thanks in advance.. :wink: [/list]

 

[jindon]

try

Private Sub Button24_Click()
Dim txt As String, r As Range, i As Integer, myTxt As String

   If WorksheetFunction.CountA(Range("b3:b9")) <> 7 Then Exit Sub
   Range("b11") = Empty
   With Range("b3")
      For i = 0 To 6 : txt = txt & r.Offset(i).Text & "_" : Next
   End With
   For Each r In Range("s3", Cells(3, Columns.Count).End(xlToLeft))
      For i = 0 To 6 : myTxt = myTxt & r.Offset(i).Text & "_" : Next
      If txt = myTxt Then
         Range("b11").Value = r.Offset(7).Value
         Exit For
      End If
   Next
End With
End Sub

 

[everscern]

When i copied that, the msg box appears ' Compile error ; End with without with'

 

[jindon]

OOps

Can you just delete Last "End With" one line above End Sub ?

 

[everscern]

now the pop up box show ' run time error 91, object variable or with block variable not set '


i modified the code u gave me and got this:

Private Sub Button24_Click()
Dim txt As String, r As Range

If WorksheetFunction.CountA(Range("b3:b9")) <> 7 Then Exit Sub
Range("b10") = Empty
txt = Range("b3").Text & "_" & Range("b4").Text & "_" & Range("b5").Text & "_" & Range("b6").Text & "_" & Range("b7").Text & "_" & Range("b8").Text & Range("b9").Text
For Each r In Range("s3", Cells(11, Columns.Count).End(xlToLeft))
If txt = r.Text & "_" & r.Offset(1).Text & "_" & r.Offset(2).Text & "_" & r.Offset(3).Text & "_" & r.Offset(4).Text & "_" & r.Offset(5).Text & "_" & r.Offset(6).Text Then
Range("b10").Value = r.Offset(7).Value
Exit For
End If
Next

End Sub


btw, thanks. we're getting there

 

[jindon]

Which code are you using?

Private Sub Button24_Click()
Dim txt As String, r As Range, i As Integer, myTxt As String

   If WorksheetFunction.CountA(Range("b3:b9")) <> 7 Then Exit Sub
   Range("b11") = Empty
   With Range("b3")
      For i = 0 To 6 : txt = txt & r.Offset(i).Text & "_" : Next
   End With
   For Each r In Range("s3", Cells(3, Columns.Count).End(xlToLeft))
      For i = 0 To 6 : myTxt = myTxt & r.Offset(i).Text & "_" : Next
      If txt = myTxt Then
         Range("b11").Value = r.Offset(7).Value
         Exit For
      End If
   Next
End Sub

 

[everscern]

i've got it!!

Private Sub Button24_Click()
Dim txt As String, r As Range

If WorksheetFunction.CountA(Range("b3:b9")) <> 7 Then Exit Sub
Range("b10") = Empty
txt = Range("b3").Text & "_" & Range("b4").Text & "_" & Range("b5").Text & "_" & Range("b6").Text & "_" & Range("b7").Text & "_" & Range("b8").Text & "_" & Range("b9").Text
For Each r In Range("s3", Cells(2, Columns.Count).End(xlToLeft))
If txt = r.Text & "_" & r.Offset(1).Text & "_" & r.Offset(2).Text & "_" & r.Offset(3).Text & "_" & r.Offset(4).Text & "_" & r.Offset(5).Text & "_" & r.Offset(6).Text Then
Range("b10").Value = r.Offset(7).Value
Exit For
End If
Next

End Sub

 

[everscern]

Hi. sorry to trouble you again and again. I'm really thankful that you're so patient with me. . Anyway, I would want to modify the code to have it only comparing within it's own ranges. look at B36. it should be 20. but it turned out to be 6

Currently the code i'm using is this:

Private Sub SMIDialog_Button18_Click()
Dim txt As String, r As Range

If WorksheetFunction.CountA(Range("b31:b35")) <> 5 Then Exit Sub
Range("b36") = Empty
txt = Range("b31").Text & "_" & Range("b32").Text & "_" & Range("b33").Text & "_" & Range("b34").Text & "_" & Range("b35").Text
For Each r In Range(("S31"), Cells(2, Columns.Count).End(xlToLeft))
If txt = r.Text & "_" & r.Offset(1).Text & "_" & r.Offset(2).Text & "_" & r.Offset(3).Text & "_" & r.Offset(4).Text Then
Range("b36").Value = r.Offset(5).Value
Exit For
End If
Next

End Sub