making this automatic

everscern

Board Regular
Joined
Oct 10, 2006
Messages
56
Code:
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!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Tyler_Dirton

Board Regular
Joined
Oct 24, 2006
Messages
50
When you say make it "automatic", do you mean when a cell is changed, or when the worksheet is opened/activated, etc?

Thanks
 

Tyler_Dirton

Board Regular
Joined
Oct 24, 2006
Messages
50
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

Board Regular
Joined
Oct 10, 2006
Messages
56

ADVERTISEMENT



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

Board Regular
Joined
Oct 24, 2006
Messages
50
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

try
Code:
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

Board Regular
Joined
Oct 10, 2006
Messages
56
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

Board Regular
Joined
Oct 10, 2006
Messages
56
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

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

Watch MrExcel Video

Forum statistics

Threads
1,109,040
Messages
5,526,409
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top