VBA Project Help

[dadalka]

This is Module 111 and I want to record a macro to change (Example) ElseIf (Range("G11").Value = 20) Then

Change 20 to 22. But I need to do the same with Module 141, Module 26, Module 51 and Module 87 all in the same workbook. Can you assit.



Sub Top10Mega4()



If (Range("G11").Value = 9) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 6) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 24) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 11) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 16) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 4) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 8) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 25) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 23) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 18) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 20) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 17) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 2) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 1) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 14) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 3) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 7) Then



Range("AS6").Select

ActiveCell.Value = ("1")



ElseIf (Range("G11").Value = 19) Then



Range("AS6").Select

ActiveCell.Value = ("1")



End If



End Sub

 

[MikeVol]

Hello @dadalka.
Why do you need so many modules in a workbook when you can use a worksheet event, shorten and optimize the code. I didn't understand the rest because in your code 1 (one) is always unchanged, and the meaning of the code is not about anything.
Enter the following code in the sheet module where you enter the value in cell G11.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell        As Range
    On Error GoTo Exitsub

    If Not Intersect(Target, Me.Range("G11")) Is Nothing Then
        If Target.Value = "" Then GoTo Exitsub
        Application.EnableEvents = False

        Dim validValues As Variant
        validValues = Array(1, 2, 3, 4, 6, 7, 8, 9, 11, 14, 16, 17, 18, 19, 20, 23, 24, 25)

        If Not IsError(Application.Match(Target.Value, validValues, 0)) Then
            Me.Range("AS6").Value = "1"
        Else
            Me.Range("AS6").ClearContents
        End If

        Application.EnableEvents = True
    End If

Exitsub:
    Application.EnableEvents = True
End Sub

I hope I understood you correctly and was able to help you. Good luck.

 

[coulombevin]

Hi Dadalka,

Here's how I would correct your actual function:

Sub Top10Mega4()
    dim val as integer, i as integer
    val = Range("G11").value
    Select Case True
        Case (val >= 1 And val <= 9) Or val = 11 Or val = 14 Or (val >= 16 And val <= 19) or (val >= 23 And val <= 25)
            Range("AS6") = 1
        Case val = 20
            Range("AS6") = 1
            Range("G11") = 22
    End Select
End Sub

In the first Case, I did not put 20 like you had, I created a special case just for this value.

Bests regards,

Vincent