Need a minor tweak to auto populate cells based on value in another cell.

Livin404

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
743
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Good day,

I know absolutely this works in a "Private Sub". I had to move it to a Module for there are moving parts by the time I need to have these certain cells Populated. The error I'm getting is in "Me.Columns" portion of the macro.

The code is:

VBA Code: 
Sub Input_Cops()

    With Excel.Application

        If Not .Intersect(Target, Me.Columns("B")) Is Nothing Then
            Me.Columns("B").AutoFit
        End If

        If Not .Intersect(Target, Me.Columns("L")) Is Nothing Then
            .EnableEvents = False
            With Target
            If .CountLarge = 1 Then
                Select Case UCase(.Value)
                Case "2300": .Offset(0, -2).Value = "1 COP"
                Case "4600": .Offset(0, -2).Value = "2 COPS"
                Case "6900": .Offset(0, -2).Value = "3 COPS"
                Case "9200": .Offset(0, -2).Value = "4 COPS"
                End Select
            End If
        End With
            .EnableEvents = True
        End If

    End With
End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Yes, it is not in the worksheet anymore. So it can not find "Who is Me?". You have to reference to worksheet specifically like:
VBA Code: 
Worksheets("myWorksheet").Columns("B"))
 
Upvote 0
Livin404 said:
Good day,

I know absolutely this works in a "Private Sub". I had to move it to a Module for there are moving parts by the time I need to have these certain cells Populated. The error I'm getting is in "Me.Columns" portion of the macro.

The code is:

VBA Code: 
Sub Input_Cops()

    With Excel.Application

        If Not .Intersect(Target, Me.Columns("B")) Is Nothing Then
            Me.Columns("B").AutoFit
        End If

        If Not .Intersect(Target, Me.Columns("L")) Is Nothing Then
            .EnableEvents = False
            With Target
            If .CountLarge = 1 Then
                Select Case UCase(.Value)
                Case "2300": .Offset(0, -2).Value = "1 COP"
                Case "4600": .Offset(0, -2).Value = "2 COPS"
                Case "6900": .Offset(0, -2).Value = "3 COPS"
                Case "9200": .Offset(0, -2).Value = "4 COPS"
                End Select
            End If
        End With
            .EnableEvents = True
        End If

    End With
End Sub
Click to expand...
Flashbond said:
Yes, it is not in the worksheet anymore. So it can not find "Who is Me?". You have to reference to worksheet specifically like:
VBA Code: 
Worksheets("myWorksheet").Columns("B"))
Click to expand...
Thank you, I read something that Me. doesn't relate to an Object.
I tried putting in where the word "Me" was being used. However top line text is appearing in red.

Thank you,

VBA Code: 
Worksheets("Outbound").Columns("B") Is Nothing Then
            Worksheets("Outbound").Columns("B").AutoFit
        End If
 
Upvote 0
Ohh, also you have Target. So in this case you have to pass the Target as reference to your function:
In the sheet use the example code below:
VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Call test(Target)
End Sub

Then modify your module like this:
VBA Code: 
Public Sub Input_Cops(t As Range)

    With Excel.Application

        If Not .Intersect(t, t.Worksheet.Columns("B")) Is Nothing Then
            Me.Columns("B").AutoFit
        End If

        If Not .Intersect(t, t.Worksheet.Columns("L")) Is Nothing Then
            .EnableEvents = False
            With Target
            If .CountLarge = 1 Then
                Select Case UCase(.Value)
                Case "2300": .Offset(0, -2).Value = "1 COP"
                Case "4600": .Offset(0, -2).Value = "2 COPS"
                Case "6900": .Offset(0, -2).Value = "3 COPS"
                Case "9200": .Offset(0, -2).Value = "4 COPS"
                End Select
            End If
        End With
            .EnableEvents = True
        End If

    End With
End Sub
 
Upvote 0
Flashbond said:
Ohh, also you have Target. So in this case you have to pass the Target as reference to your function:
In the sheet use the example code below:
VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Call test(Target)
End Sub

Then modify your module like this:
VBA Code: 
Public Sub Input_Cops(t As Range)

    With Excel.Application

        If Not .Intersect(t, t.Worksheet.Columns("B")) Is Nothing Then
            Me.Columns("B").AutoFit
        End If

        If Not .Intersect(t, t.Worksheet.Columns("L")) Is Nothing Then
            .EnableEvents = False
            With Target
            If .CountLarge = 1 Then
                Select Case UCase(.Value)
                Case "2300": .Offset(0, -2).Value = "1 COP"
                Case "4600": .Offset(0, -2).Value = "2 COPS"
                Case "6900": .Offset(0, -2).Value = "3 COPS"
                Case "9200": .Offset(0, -2).Value = "4 COPS"
                End Select
            End If
        End With
            .EnableEvents = True
        End If

    End With
End Sub
Click to expand...
I'm truly sorry, I tried everything in my mind with both those formulas, I put both those codes in "Sheet1(Outbound)" and I cannot get past the "Worksheet_Selection...". I tried putting the second one in the Module but again I'm stuck. My apologies, I'm just not familiar with this process.

Thank you,
 
Upvote 0
Flashbond said:
Ohh, also you have Target. So in this case you have to pass the Target as reference to your function:
In the sheet use the example code below:
VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Call test(Target)
End Sub

Then modify your module like this:
VBA Code: 
Public Sub Input_Cops(t As Range)

    With Excel.Application

        If Not .Intersect(t, t.Worksheet.Columns("B")) Is Nothing Then
            Me.Columns("B").AutoFit
        End If

        If Not .Intersect(t, t.Worksheet.Columns("L")) Is Nothing Then
            .EnableEvents = False
            With Target
            If .CountLarge = 1 Then
                Select Case UCase(.Value)
                Case "2300": .Offset(0, -2).Value = "1 COP"
                Case "4600": .Offset(0, -2).Value = "2 COPS"
                Case "6900": .Offset(0, -2).Value = "3 COPS"
                Case "9200": .Offset(0, -2).Value = "4 COPS"
                End Select
            End If
        End With
            .EnableEvents = True
        End If

    End With
End Sub
Click to expand...
Thank you for all your help. I actually figured a way to make it work. It may not be ideal. Nonetheless, you were very kind it help me out. I am grateful.
VBA Code: 
Sub Input_Cops()
    Dim i As Long
    For i = 12 To ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
    Select Case ActiveSheet.Cells(i, 12).Value
    Case "2300"
ActiveSheet.Range("J" & i).Value = ActiveSheet.Range("J" & i).Value & "1 COP"
    Case "4600"
ActiveSheet.Range("J" & i).Value = ActiveSheet.Range("J" & i).Value & "2 COPs"
    Case "6900"
ActiveSheet.Range("J" & i).Value = ActiveSheet.Range("J" & i).Value & "3 COPs"
    Case "9200"
ActiveSheet.Range("J" & i).Value = ActiveSheet.Range("J" & i).Value & "4 COPs"
End Select
Next i
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

Livin404
  • Question
Running Total
Replies
0
Views
177
Livin404
Livin404
W
  • Solved
Code for rows of cells returning reverse-order values.
Replies
5
Views
211
Worksong
W
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
608
kevin9999
K
S
  • Solved
Need to have Worksheet_Change event include value from another column using structured reference
Replies
4
Views
328
Alex Blakenburg
A
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
433
sspatriots
S

Forum statistics

Threads
1,216,099
Messages
6,128,820
Members
449,469
Latest member
Kingwi11y

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top