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

Livin404

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
774
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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 Question
Current Macro does does not completely go through all rows.
Replies
6
Views
183
Joe4
Joe4
cpg84
  • Question Question
Merging and unmerging cells based on another cell's value
Replies
2
Views
293
cpg84
cpg84
E
  • Question Question
Copy Cell as Hyperlink to another cell
Replies
1
Views
241
eli_m
E
J
  • Solved
Run time error for Worksheet Change in VBA
2
Replies
17
Views
430
jtemp57
J
C
  • Solved
VBA code for changes in cell and delete cell value
2
Replies
10
Views
279
cooleomter
C

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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