Excel VBA give numbers to specified cells, avoid Select

up vote
1
down vote

favorite

I have following VBA code in Excel. It is a part of very long VBA responsible for a lot of processes. How I can get rid of Select command? I have tried several things with no success.

ActiveWorkbook.Sheets("Installation").Select

    For Each Cl14 In ThisWorkbook.Sheets("Installation").Range("G2", Range("G" & Rows.Count).End(xlUp))

      Select Case LCase(Cl14.Value)

         Case "main"

            m14 = m14 + 1: s14 = 0: ss14 = 0

            Cl14.Offset(, -6).Value = m14

         Case "sub"

            s14 = s14 + 1: ss14 = 0

            Cl14.Offset(, -6) = m14 & "." & s14

         Case "sub-sub"

            ss14 = ss14 + 1

            Cl14.Offset(, -6) = m14 & "." & s14 & "." & ss14

      End Select

   Next Cl14

excel vba excel-vba optimization

share|improve this question

asked 3 hours ago

user7202022

867

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Are you saying that, if you replace ThisWorkbook by ActiveWorkbook and you delete the line with Select, that is doesn't work?
  – Dominique
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Oh, I just see a Rows.count in your For Each loop. What does this Rows refer to?
  – Dominique
  1 hour ago
  

  
  
  
  

add a comment | 

up vote
1
down vote

favorite

I have following VBA code in Excel. It is a part of very long VBA responsible for a lot of processes. How I can get rid of Select command? I have tried several things with no success.

ActiveWorkbook.Sheets("Installation").Select

    For Each Cl14 In ThisWorkbook.Sheets("Installation").Range("G2", Range("G" & Rows.Count).End(xlUp))

      Select Case LCase(Cl14.Value)

         Case "main"

            m14 = m14 + 1: s14 = 0: ss14 = 0

            Cl14.Offset(, -6).Value = m14

         Case "sub"

            s14 = s14 + 1: ss14 = 0

            Cl14.Offset(, -6) = m14 & "." & s14

         Case "sub-sub"

            ss14 = ss14 + 1

            Cl14.Offset(, -6) = m14 & "." & s14 & "." & ss14

      End Select

   Next Cl14

excel vba excel-vba optimization

share|improve this question

asked 3 hours ago

user7202022

867

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Are you saying that, if you replace ThisWorkbook by ActiveWorkbook and you delete the line with Select, that is doesn't work?
  – Dominique
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Oh, I just see a Rows.count in your For Each loop. What does this Rows refer to?
  – Dominique
  1 hour ago
  

  
  
  
  

add a comment | 

up vote
1
down vote

favorite

up vote
1
down vote

favorite

I have following VBA code in Excel. It is a part of very long VBA responsible for a lot of processes. How I can get rid of Select command? I have tried several things with no success.

ActiveWorkbook.Sheets("Installation").Select

    For Each Cl14 In ThisWorkbook.Sheets("Installation").Range("G2", Range("G" & Rows.Count).End(xlUp))

      Select Case LCase(Cl14.Value)

         Case "main"

            m14 = m14 + 1: s14 = 0: ss14 = 0

            Cl14.Offset(, -6).Value = m14

         Case "sub"

            s14 = s14 + 1: ss14 = 0

            Cl14.Offset(, -6) = m14 & "." & s14

         Case "sub-sub"

            ss14 = ss14 + 1

            Cl14.Offset(, -6) = m14 & "." & s14 & "." & ss14

      End Select

   Next Cl14

excel vba excel-vba optimization

share|improve this question

asked 3 hours ago

user7202022

867

I have following VBA code in Excel. It is a part of very long VBA responsible for a lot of processes. How I can get rid of Select command? I have tried several things with no success.

ActiveWorkbook.Sheets("Installation").Select

    For Each Cl14 In ThisWorkbook.Sheets("Installation").Range("G2", Range("G" & Rows.Count).End(xlUp))

      Select Case LCase(Cl14.Value)

         Case "main"

            m14 = m14 + 1: s14 = 0: ss14 = 0

            Cl14.Offset(, -6).Value = m14

         Case "sub"

            s14 = s14 + 1: ss14 = 0

            Cl14.Offset(, -6) = m14 & "." & s14

         Case "sub-sub"

            ss14 = ss14 + 1

            Cl14.Offset(, -6) = m14 & "." & s14 & "." & ss14

      End Select

   Next Cl14

excel vba excel-vba optimization

excel vba excel-vba optimization

share|improve this question

asked 3 hours ago

user7202022

867

share|improve this question

asked 3 hours ago

user7202022

867

share|improve this question

share|improve this question

asked 3 hours ago

user7202022

867

asked 3 hours ago

user7202022

867

asked 3 hours ago

user7202022

867

867

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Are you saying that, if you replace ThisWorkbook by ActiveWorkbook and you delete the line with Select, that is doesn't work?
  – Dominique
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Oh, I just see a Rows.count in your For Each loop. What does this Rows refer to?
  – Dominique
  1 hour ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Are you saying that, if you replace ThisWorkbook by ActiveWorkbook and you delete the line with Select, that is doesn't work?
  – Dominique
  1 hour ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Oh, I just see a Rows.count in your For Each loop. What does this Rows refer to?
  – Dominique
  1 hour ago
  

  
  
  
  

Are you saying that, if you replace ThisWorkbook by ActiveWorkbook and you delete the line with Select, that is doesn't work?
– Dominique
1 hour ago

Are you saying that, if you replace ThisWorkbook by ActiveWorkbook and you delete the line with Select, that is doesn't work?
– Dominique
1 hour ago

Oh, I just see a Rows.count in your For Each loop. What does this Rows refer to?
– Dominique
1 hour ago

Oh, I just see a Rows.count in your For Each loop. What does this Rows refer to?
– Dominique
1 hour ago

add a comment | 

2 Answers
2

active

oldest

votes

up vote
0
down vote

Wrap the Range objects using a With statement. Consider replacing your variable names with something more meaningful. I have only changed one. Also, use Option Explicit at the top of your code and declare all your variables with type.

Option Explicit

Public Sub test()

    Dim ws As Worksheet, iCell As Range, m14 As Long, s14 As Long, ss14 As Long

    Set ws = ThisWorkbook.Worksheets("Installation")



    With ws

        For Each iCell In .Range("G2", .Range("G" & .Rows.Count).End(xlUp))

            Select Case LCase(iCell.Value)

            Case "main"

                m14 = m14 + 1: s14 = 0: ss14 = 0

                iCell.Offset(, -6).Value = m14

            Case "sub"

                s14 = s14 + 1: ss14 = 0

                iCell.Offset(, -6) = m14 & "." & s14

            Case "sub-sub"

                ss14 = ss14 + 1

                iCell.Offset(, -6) = m14 & "." & s14 & "." & ss14

            End Select

        Next iCell

    End With

End Sub

share|improve this answer

answered 3 hours ago

QHarr

25.6k81839

add a comment | 

up vote
0
down vote

can do a force loop to prevent the select case. But personally i like to use select case more.

For i = 2 To .Range("G" & .Rows.Count).End(xlUp).Row

If .Range("G" & i).Value = "main" Then

'code here

ElseIf .Range("G" & i).Value = "sub" Then

'code here

ElseIf .Range("G" & i).Value = "sub-sub" Then

'code here

End If

Next i

share|improve this answer

answered 3 hours ago

Osman Wong

495

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53371264%2fexcel-vba-give-numbers-to-specified-cells-avoid-select%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
0
down vote

Wrap the Range objects using a With statement. Consider replacing your variable names with something more meaningful. I have only changed one. Also, use Option Explicit at the top of your code and declare all your variables with type.

Option Explicit

Public Sub test()

    Dim ws As Worksheet, iCell As Range, m14 As Long, s14 As Long, ss14 As Long

    Set ws = ThisWorkbook.Worksheets("Installation")



    With ws

        For Each iCell In .Range("G2", .Range("G" & .Rows.Count).End(xlUp))

            Select Case LCase(iCell.Value)

            Case "main"

                m14 = m14 + 1: s14 = 0: ss14 = 0

                iCell.Offset(, -6).Value = m14

            Case "sub"

                s14 = s14 + 1: ss14 = 0

                iCell.Offset(, -6) = m14 & "." & s14

            Case "sub-sub"

                ss14 = ss14 + 1

                iCell.Offset(, -6) = m14 & "." & s14 & "." & ss14

            End Select

        Next iCell

    End With

End Sub

share|improve this answer

answered 3 hours ago

QHarr

25.6k81839

add a comment | 

up vote
0
down vote

Wrap the Range objects using a With statement. Consider replacing your variable names with something more meaningful. I have only changed one. Also, use Option Explicit at the top of your code and declare all your variables with type.

Option Explicit

Public Sub test()

    Dim ws As Worksheet, iCell As Range, m14 As Long, s14 As Long, ss14 As Long

    Set ws = ThisWorkbook.Worksheets("Installation")



    With ws

        For Each iCell In .Range("G2", .Range("G" & .Rows.Count).End(xlUp))

            Select Case LCase(iCell.Value)

            Case "main"

                m14 = m14 + 1: s14 = 0: ss14 = 0

                iCell.Offset(, -6).Value = m14

            Case "sub"

                s14 = s14 + 1: ss14 = 0

                iCell.Offset(, -6) = m14 & "." & s14

            Case "sub-sub"

                ss14 = ss14 + 1

                iCell.Offset(, -6) = m14 & "." & s14 & "." & ss14

            End Select

        Next iCell

    End With

End Sub

share|improve this answer

answered 3 hours ago

QHarr

25.6k81839

add a comment | 

up vote
0
down vote

up vote
0
down vote

Wrap the Range objects using a With statement. Consider replacing your variable names with something more meaningful. I have only changed one. Also, use Option Explicit at the top of your code and declare all your variables with type.

Option Explicit

Public Sub test()

    Dim ws As Worksheet, iCell As Range, m14 As Long, s14 As Long, ss14 As Long

    Set ws = ThisWorkbook.Worksheets("Installation")



    With ws

        For Each iCell In .Range("G2", .Range("G" & .Rows.Count).End(xlUp))

            Select Case LCase(iCell.Value)

            Case "main"

                m14 = m14 + 1: s14 = 0: ss14 = 0

                iCell.Offset(, -6).Value = m14

            Case "sub"

                s14 = s14 + 1: ss14 = 0

                iCell.Offset(, -6) = m14 & "." & s14

            Case "sub-sub"

                ss14 = ss14 + 1

                iCell.Offset(, -6) = m14 & "." & s14 & "." & ss14

            End Select

        Next iCell

    End With

End Sub

share|improve this answer

answered 3 hours ago

QHarr

25.6k81839

Wrap the Range objects using a With statement. Consider replacing your variable names with something more meaningful. I have only changed one. Also, use Option Explicit at the top of your code and declare all your variables with type.

Option Explicit

Public Sub test()

    Dim ws As Worksheet, iCell As Range, m14 As Long, s14 As Long, ss14 As Long

    Set ws = ThisWorkbook.Worksheets("Installation")



    With ws

        For Each iCell In .Range("G2", .Range("G" & .Rows.Count).End(xlUp))

            Select Case LCase(iCell.Value)

            Case "main"

                m14 = m14 + 1: s14 = 0: ss14 = 0

                iCell.Offset(, -6).Value = m14

            Case "sub"

                s14 = s14 + 1: ss14 = 0

                iCell.Offset(, -6) = m14 & "." & s14

            Case "sub-sub"

                ss14 = ss14 + 1

                iCell.Offset(, -6) = m14 & "." & s14 & "." & ss14

            End Select

        Next iCell

    End With

End Sub

share|improve this answer

answered 3 hours ago

QHarr

25.6k81839

share|improve this answer

share|improve this answer

answered 3 hours ago

QHarr

25.6k81839

answered 3 hours ago

QHarr

25.6k81839

answered 3 hours ago

QHarr

25.6k81839

25.6k81839

add a comment | 

add a comment | 

up vote
0
down vote

can do a force loop to prevent the select case. But personally i like to use select case more.

For i = 2 To .Range("G" & .Rows.Count).End(xlUp).Row

If .Range("G" & i).Value = "main" Then

'code here

ElseIf .Range("G" & i).Value = "sub" Then

'code here

ElseIf .Range("G" & i).Value = "sub-sub" Then

'code here

End If

Next i

share|improve this answer

answered 3 hours ago

Osman Wong

495

add a comment | 

up vote
0
down vote

can do a force loop to prevent the select case. But personally i like to use select case more.

For i = 2 To .Range("G" & .Rows.Count).End(xlUp).Row

If .Range("G" & i).Value = "main" Then

'code here

ElseIf .Range("G" & i).Value = "sub" Then

'code here

ElseIf .Range("G" & i).Value = "sub-sub" Then

'code here

End If

Next i

share|improve this answer

answered 3 hours ago

Osman Wong

495

add a comment | 

up vote
0
down vote

up vote
0
down vote

can do a force loop to prevent the select case. But personally i like to use select case more.

For i = 2 To .Range("G" & .Rows.Count).End(xlUp).Row

If .Range("G" & i).Value = "main" Then

'code here

ElseIf .Range("G" & i).Value = "sub" Then

'code here

ElseIf .Range("G" & i).Value = "sub-sub" Then

'code here

End If

Next i

share|improve this answer

answered 3 hours ago

Osman Wong

495

can do a force loop to prevent the select case. But personally i like to use select case more.

For i = 2 To .Range("G" & .Rows.Count).End(xlUp).Row

If .Range("G" & i).Value = "main" Then

'code here

ElseIf .Range("G" & i).Value = "sub" Then

'code here

ElseIf .Range("G" & i).Value = "sub-sub" Then

'code here

End If

Next i

share|improve this answer

answered 3 hours ago

Osman Wong

495

share|improve this answer

share|improve this answer

answered 3 hours ago

Osman Wong

495

answered 3 hours ago

Osman Wong

495

answered 3 hours ago

Osman Wong

495

495

add a comment | 

add a comment | 

 

draft saved

draft discarded

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53371264%2fexcel-vba-give-numbers-to-specified-cells-avoid-select%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here