Variable not defined problem with option explicit macro

I have the below code which works with a simple sheet. If I try and incorporate it into my macro, which is option explicit then it throws an error that variable x is not defined.

Have tried various Dim as but without success. New to this so I assume I am missing something, probably fairly obvious to you guys

Sub FillDuplicates()

Dim lastrow As Long



lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'find last row in column A



For x = 1 To lastrow

    If Cells(x, 1).Value = 0 Then 'Find initial 0 value



            If Cells(x + 1, 1).Value = 0 Then 'Compares cell against previous cell

                Cells(x + 1, 1).Value = "" 'If matches, sets value as nothing

            End If



    End If

Next x



End Sub

This identifies where there are a number of consecutive 0 values in the column. It then retains the first and deletes the others, then moves on to the next group and so on.

edited Jan 2 at 7:48

Andreas

16.7k41744

asked Jan 2 at 7:46

Fergus Farbridge

1

x looks like an integer to me. So try Dim x as Interger or possibly long, depending on how many rows you expect there to be. Integer has a Max of ~32000 if I remember correctly

– Andreas
Jan 2 at 7:49

5

The x goes from 1 to lastrow where lastrow is Long. So x also should be Long. So Dim x As Long before For x = 1 To lastrow.

– Axel Richter
Jan 2 at 7:55

Definitely Long as it's using row numbers which can exceed the limit of an Integer. Also because lastrow is defined as Long, so makes sense that a variable that steps through each value in lastrow should also be Long.

– Darren Bartrup-Cook
Jan 2 at 10:39

add a comment |

I have the below code which works with a simple sheet. If I try and incorporate it into my macro, which is option explicit then it throws an error that variable x is not defined.

Have tried various Dim as but without success. New to this so I assume I am missing something, probably fairly obvious to you guys

Sub FillDuplicates()

Dim lastrow As Long



lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'find last row in column A



For x = 1 To lastrow

    If Cells(x, 1).Value = 0 Then 'Find initial 0 value



            If Cells(x + 1, 1).Value = 0 Then 'Compares cell against previous cell

                Cells(x + 1, 1).Value = "" 'If matches, sets value as nothing

            End If



    End If

Next x



End Sub

This identifies where there are a number of consecutive 0 values in the column. It then retains the first and deletes the others, then moves on to the next group and so on.

edited Jan 2 at 7:48

Andreas

16.7k41744

asked Jan 2 at 7:46

Fergus Farbridge

1

x looks like an integer to me. So try Dim x as Interger or possibly long, depending on how many rows you expect there to be. Integer has a Max of ~32000 if I remember correctly

– Andreas
Jan 2 at 7:49

5

The x goes from 1 to lastrow where lastrow is Long. So x also should be Long. So Dim x As Long before For x = 1 To lastrow.

– Axel Richter
Jan 2 at 7:55

Definitely Long as it's using row numbers which can exceed the limit of an Integer. Also because lastrow is defined as Long, so makes sense that a variable that steps through each value in lastrow should also be Long.

– Darren Bartrup-Cook
Jan 2 at 10:39

add a comment |

I have the below code which works with a simple sheet. If I try and incorporate it into my macro, which is option explicit then it throws an error that variable x is not defined.

Have tried various Dim as but without success. New to this so I assume I am missing something, probably fairly obvious to you guys

Sub FillDuplicates()

Dim lastrow As Long



lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'find last row in column A



For x = 1 To lastrow

    If Cells(x, 1).Value = 0 Then 'Find initial 0 value



            If Cells(x + 1, 1).Value = 0 Then 'Compares cell against previous cell

                Cells(x + 1, 1).Value = "" 'If matches, sets value as nothing

            End If



    End If

Next x



End Sub

This identifies where there are a number of consecutive 0 values in the column. It then retains the first and deletes the others, then moves on to the next group and so on.

edited Jan 2 at 7:48

Andreas

16.7k41744

asked Jan 2 at 7:46

Fergus Farbridge

I have the below code which works with a simple sheet. If I try and incorporate it into my macro, which is option explicit then it throws an error that variable x is not defined.

Have tried various Dim as but without success. New to this so I assume I am missing something, probably fairly obvious to you guys

Sub FillDuplicates()

Dim lastrow As Long



lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'find last row in column A



For x = 1 To lastrow

    If Cells(x, 1).Value = 0 Then 'Find initial 0 value



            If Cells(x + 1, 1).Value = 0 Then 'Compares cell against previous cell

                Cells(x + 1, 1).Value = "" 'If matches, sets value as nothing

            End If



    End If

Next x



End Sub

This identifies where there are a number of consecutive 0 values in the column. It then retains the first and deletes the others, then moves on to the next group and so on.

excel vba

edited Jan 2 at 7:48

Andreas

16.7k41744

asked Jan 2 at 7:46

Fergus Farbridge

edited Jan 2 at 7:48

Andreas

16.7k41744

asked Jan 2 at 7:46

Fergus Farbridge

edited Jan 2 at 7:48

Andreas

16.7k41744

edited Jan 2 at 7:48

Andreas

16.7k41744

edited Jan 2 at 7:48

Andreas

16.7k41744

asked Jan 2 at 7:46

Fergus Farbridge

asked Jan 2 at 7:46

Fergus Farbridge

asked Jan 2 at 7:46

Fergus Farbridge

1

x looks like an integer to me. So try Dim x as Interger or possibly long, depending on how many rows you expect there to be. Integer has a Max of ~32000 if I remember correctly

– Andreas
Jan 2 at 7:49

5

The x goes from 1 to lastrow where lastrow is Long. So x also should be Long. So Dim x As Long before For x = 1 To lastrow.

– Axel Richter
Jan 2 at 7:55

Definitely Long as it's using row numbers which can exceed the limit of an Integer. Also because lastrow is defined as Long, so makes sense that a variable that steps through each value in lastrow should also be Long.

– Darren Bartrup-Cook
Jan 2 at 10:39

add a comment |

1

x looks like an integer to me. So try Dim x as Interger or possibly long, depending on how many rows you expect there to be. Integer has a Max of ~32000 if I remember correctly

– Andreas
Jan 2 at 7:49

5

The x goes from 1 to lastrow where lastrow is Long. So x also should be Long. So Dim x As Long before For x = 1 To lastrow.

– Axel Richter
Jan 2 at 7:55

Definitely Long as it's using row numbers which can exceed the limit of an Integer. Also because lastrow is defined as Long, so makes sense that a variable that steps through each value in lastrow should also be Long.

– Darren Bartrup-Cook
Jan 2 at 10:39

x looks like an integer to me. So try Dim x as Interger or possibly long, depending on how many rows you expect there to be. Integer has a Max of ~32000 if I remember correctly

– Andreas
Jan 2 at 7:49

The x goes from 1 to lastrow where lastrow is Long. So x also should be Long. So Dim x As Long before For x = 1 To lastrow.

– Axel Richter
Jan 2 at 7:55

Definitely Long as it's using row numbers which can exceed the limit of an Integer. Also because lastrow is defined as Long, so makes sense that a variable that steps through each value in lastrow should also be Long.

– Darren Bartrup-Cook
Jan 2 at 10:39

add a comment |

1 Answer
1

active

oldest

votes

As Andreas before me:
You have to define the x variable:

Sub FillDuplicates()

Dim lastrow As Long



Dim x as Integer 'if you have more 32767 rows to check then use double which is good for 1.79769313486231570E+308



lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'find last row in column A



For x = 1 To lastrow

    If Cells(x, 1).Value = 0 Then 'Find initial 0 value



            If Cells(x + 1, 1).Value = 0 Then 'Compares cell against previous cell

                Cells(x + 1, 1).Value = "" 'If matches, sets value as nothing

            End If



    End If

Next x



End Sub

This should solve the problem :)

edited Jan 2 at 10:41

Darren Bartrup-Cook

14k11533

answered Jan 2 at 7:55

Lonolian

749

What i variable? There is no i in the code

– Andreas
Jan 2 at 7:56

1

S/he means x, not i. Basically, the error occurs because you're using x as your loop counter: but you have not Dim'med it. You need to declare every variable you use, and loop counter variables are no exception. Lonolian is correct, but just made a typo, using "i" instead of "x".

– Chris Melville
Jan 2 at 9:41

Thankyou all Dim as integer solved it.

– Fergus Farbridge
Jan 2 at 19:27

Thank you Darren Bartup-Cook and Chris Melville

– Lonolian
Jan 3 at 14:01

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',
autoActivateHeartbeat: false,
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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54002873%2fvariable-not-defined-problem-with-option-explicit-macro%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

As Andreas before me:
You have to define the x variable:

Sub FillDuplicates()

Dim lastrow As Long



Dim x as Integer 'if you have more 32767 rows to check then use double which is good for 1.79769313486231570E+308



lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'find last row in column A



For x = 1 To lastrow

    If Cells(x, 1).Value = 0 Then 'Find initial 0 value



            If Cells(x + 1, 1).Value = 0 Then 'Compares cell against previous cell

                Cells(x + 1, 1).Value = "" 'If matches, sets value as nothing

            End If



    End If

Next x



End Sub

This should solve the problem :)

edited Jan 2 at 10:41

Darren Bartrup-Cook

14k11533

answered Jan 2 at 7:55

Lonolian

749

What i variable? There is no i in the code

– Andreas
Jan 2 at 7:56

1

S/he means x, not i. Basically, the error occurs because you're using x as your loop counter: but you have not Dim'med it. You need to declare every variable you use, and loop counter variables are no exception. Lonolian is correct, but just made a typo, using "i" instead of "x".

– Chris Melville
Jan 2 at 9:41

Thankyou all Dim as integer solved it.

– Fergus Farbridge
Jan 2 at 19:27

Thank you Darren Bartup-Cook and Chris Melville

– Lonolian
Jan 3 at 14:01

add a comment |

As Andreas before me:
You have to define the x variable:

Sub FillDuplicates()

Dim lastrow As Long



Dim x as Integer 'if you have more 32767 rows to check then use double which is good for 1.79769313486231570E+308



lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'find last row in column A



For x = 1 To lastrow

    If Cells(x, 1).Value = 0 Then 'Find initial 0 value



            If Cells(x + 1, 1).Value = 0 Then 'Compares cell against previous cell

                Cells(x + 1, 1).Value = "" 'If matches, sets value as nothing

            End If



    End If

Next x



End Sub

This should solve the problem :)

edited Jan 2 at 10:41

Darren Bartrup-Cook

14k11533

answered Jan 2 at 7:55

Lonolian

749

What i variable? There is no i in the code

– Andreas
Jan 2 at 7:56

1

S/he means x, not i. Basically, the error occurs because you're using x as your loop counter: but you have not Dim'med it. You need to declare every variable you use, and loop counter variables are no exception. Lonolian is correct, but just made a typo, using "i" instead of "x".

– Chris Melville
Jan 2 at 9:41

Thankyou all Dim as integer solved it.

– Fergus Farbridge
Jan 2 at 19:27

Thank you Darren Bartup-Cook and Chris Melville

– Lonolian
Jan 3 at 14:01

add a comment |

As Andreas before me:
You have to define the x variable:

Sub FillDuplicates()

Dim lastrow As Long



Dim x as Integer 'if you have more 32767 rows to check then use double which is good for 1.79769313486231570E+308



lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'find last row in column A



For x = 1 To lastrow

    If Cells(x, 1).Value = 0 Then 'Find initial 0 value



            If Cells(x + 1, 1).Value = 0 Then 'Compares cell against previous cell

                Cells(x + 1, 1).Value = "" 'If matches, sets value as nothing

            End If



    End If

Next x



End Sub

This should solve the problem :)

edited Jan 2 at 10:41

Darren Bartrup-Cook

14k11533

answered Jan 2 at 7:55

Lonolian

749

As Andreas before me:
You have to define the x variable:

Sub FillDuplicates()

Dim lastrow As Long



Dim x as Integer 'if you have more 32767 rows to check then use double which is good for 1.79769313486231570E+308



lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'find last row in column A



For x = 1 To lastrow

    If Cells(x, 1).Value = 0 Then 'Find initial 0 value



            If Cells(x + 1, 1).Value = 0 Then 'Compares cell against previous cell

                Cells(x + 1, 1).Value = "" 'If matches, sets value as nothing

            End If



    End If

Next x



End Sub

This should solve the problem :)

edited Jan 2 at 10:41

Darren Bartrup-Cook

14k11533

answered Jan 2 at 7:55

Lonolian

749

edited Jan 2 at 10:41

Darren Bartrup-Cook

14k11533

edited Jan 2 at 10:41

Darren Bartrup-Cook

14k11533

edited Jan 2 at 10:41

Darren Bartrup-Cook

14k11533

answered Jan 2 at 7:55

Lonolian

749

answered Jan 2 at 7:55

Lonolian

749

answered Jan 2 at 7:55

Lonolian

749

What i variable? There is no i in the code

– Andreas
Jan 2 at 7:56

1

S/he means x, not i. Basically, the error occurs because you're using x as your loop counter: but you have not Dim'med it. You need to declare every variable you use, and loop counter variables are no exception. Lonolian is correct, but just made a typo, using "i" instead of "x".

– Chris Melville
Jan 2 at 9:41

Thankyou all Dim as integer solved it.

– Fergus Farbridge
Jan 2 at 19:27

Thank you Darren Bartup-Cook and Chris Melville

– Lonolian
Jan 3 at 14:01

add a comment |

What i variable? There is no i in the code

– Andreas
Jan 2 at 7:56

1

S/he means x, not i. Basically, the error occurs because you're using x as your loop counter: but you have not Dim'med it. You need to declare every variable you use, and loop counter variables are no exception. Lonolian is correct, but just made a typo, using "i" instead of "x".

– Chris Melville
Jan 2 at 9:41

Thankyou all Dim as integer solved it.

– Fergus Farbridge
Jan 2 at 19:27

Thank you Darren Bartup-Cook and Chris Melville

– Lonolian
Jan 3 at 14:01

What i variable? There is no i in the code

– Andreas
Jan 2 at 7:56

S/he means x, not i. Basically, the error occurs because you're using x as your loop counter: but you have not Dim'med it. You need to declare every variable you use, and loop counter variables are no exception. Lonolian is correct, but just made a typo, using "i" instead of "x".

– Chris Melville
Jan 2 at 9:41

Thankyou all Dim as integer solved it.

– Fergus Farbridge
Jan 2 at 19:27

Thank you Darren Bartup-Cook and Chris Melville

– Lonolian
Jan 3 at 14:01

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Sign up or log in

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

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

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

Search This Blog

Ufyukyu