making this automatic

[everscern]

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(3, 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

hi. how do i go about making this automatic, maybe thru looping? without having to depend on a button?

Thanks!

 

[Tyler_Dirton]

When you say make it "automatic", do you mean when a cell is changed, or when the worksheet is opened/activated, etc?

Thanks

 

[everscern]

it means when the cell is changed. Thanks

 

[Tyler_Dirton]

If you right-click the sheet name, and select 'view code', then copy your code as above replacing

Private Sub Button24_Click()

with

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

By calling this procedure, this should enable the automation of your macro when the cell value is altered.

 

[everscern]

hi. this dialog box pops up when i add this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

twice in a worksheet. is there any way to clear this mess?

 

[Tyler_Dirton]

My understanding is that you cannot have two occurences of
''Private Sub Worksheet_SelectionChange" in one worksheet.

Therefore, include your next 'If' statement
[If WorksheetFunction.CountA(Range("b17:b23")) <> 7]
after the 'End If' line in your initial coding Sub.

Hope this helps

 

[jindon]

try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim txt As String, r As Range
   If Intersect(Target, Range("b3:b9")) Is Nothing Then Exit Sub
   If WorksheetFunction.CountA(Range("b3:b9")) <> 7 Then Exit Sub
   Application.EnableEvents = False
   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(3, 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
   Application.EnableEvents = True
End Sub

 

[everscern]

I'm sorry but it still did not work for both of your suggestions.

It showed ' typed mismatched' or nth appeared in sheet 2.

Thanks for your response

 

[jindon]

I'm sorry but it still did not work for both of your suggestions.

It showed ' typed mismatched' or nth appeared in sheet 2.

Thanks for your response

Don't understand above.

When you debug, which line was highlighted?

 

[everscern]

Private Sub Worksheet_Change(ByVal Target As Range)

this line was highlighted. the pop up box showed : complied error, ambiguous name detected : worksheet_change