My VBA code stopped working properly

[Dark0Prince]

Something went wrong with my code it only does the first column, but when i first tested this it did all columns that had data in them.

Please Login or Register  to view this content.

[Logit]

.
Except for adding a couple of DIM statements, the code works.

Having it check to row 25500 on each sheet takes a L O N G time ...

Please Login or Register  to view this content.

[Dark0Prince]

What is that option explicit? it goes into the previous sub that I have.
I don't know where to put that.

EDIT: This also never runs that many rows that is just a safeguard because i want it to do multiple columns and have 99 stop it on each column. but it just stops on first 99 check all together and doesn't loop to check for more used ranges

[Logit]

.
"Option Explicit" is a setting you can enable in Excel to automatically be placed at the top of every module / macro you use. It forces you to use proper coding / syntax as it does a 'syntax check' kinda like
auto-spelling. If you've entered a line of code that is incorrect, Option Explicit will highlight the error and provide an error code from which you might be able to discern an acceptable replacement syntax.

You can leave the comment out if you desire. Doing so may make it more difficult for you to determine where an error lies in your code, especially if you are just beginning to code.

Option Explicit always goes at the top of any module /macro ... above the statement Sub 'your sub name' ()

EDIT: This also never runs that many rows that is just a safeguard because i want it to do multiple columns and have 99 stop it on each column. but it just stops on first 99 check all together and doesn't loop to check for more used ranges

It will still search the entire range if there is data therein or not. Therefore, it will take alot longer to run if the range is set to 25500. Suggestion: if you will never be reviewing more than 99 rows, why not set the number to 100 or 150 ? as a safeguard.

[Dark0Prince]

.
It will still search the entire range if there is data therein or not. Therefore, it will take alot longer to run if the range is set to 25500. Suggestion: if you will never be reviewing more than 99 rows, why not set the number to 100 or 150 ? as a safeguard.

It still only checks the first column it doesn't seem to check anything other then column A when i want it to keep checking all columns with data. I can easily change that 25000 to something like 2000 I just didn't fix it yet.

[Logit]

,
This is the result from running the macro here. Is this the result you are expecting ?

99.jpg

[Dark0Prince]

Yes that is the result but my sheet has 99 at the end of each column so it doesn't keep going.

[Logit]

.
Try this code as replacement for the other. Then run it. You will see that it is checking all the columns.

Enter 99 at the bottom of each column first. Say, A30, B30, C30 .... R30, S30, T30.

Please Login or Register  to view this content.

[Dark0Prince]

.
Try this code as replacement for the other. Then run it. You will see that it is checking all the columns.

Enter 99 at the bottom of each column first. Say, A30, B30, C30 .... R30, S30, T30.

Please Login or Register  to view this content.

mind just checks the first column and i'm using the same code as you

[Logit]

.
There is something else going on in your project or with your computer then.

Here is the completed project ...

[Dark0Prince]

.
There is something else going on in your project or with your computer then.

Here is the completed project ...

Yours works when I download it or point my button on my work sheet to your code but I'm putting this in a personal work book for later use and it didn't work after I copy and paste.

[Logit]

Please post a copy of your personal workbook - without confidential or private data.

Show or explain where you posted the code in your workbook. If there were errors, indicate what the error / s was / were and
if the code was highlighted by the error, where in the code the highlight occurred.

Give us an opportunity to trouble shoot this for you.

[Dark0Prince]

I know what the problem is but don't know how to fix it. It's because of where I'm putting it. When i put this code in Sheet1 it works fine, but when I put it in personal workbook it doesn't work fine. Even if I put in personal workbook(Sheet1) area. There is no other code to show you in my personal workbook it just doesn't work when i put it in there.

[Logit]

What is the name of the sheet you are attempting to insert the code ?
Look at the red Sheet1 name below ...

Option Explicit

Sub practice()
Dim k, i As Integer
Dim val As String
val = ""

For k = 1 To Sheet1.UsedRange.Columns.Count '<--- Change sheet name to match

For i = 2 To 25
If Cells(i, k).Value = "" Then
Cells(i, k).Value = val
ElseIf Cells(i, k).Value = "99" Or Cells(i, k).Value = 99 Then
Exit For
Else
val = Cells(i, k).Value
End If
Next i
Next k

MsgBox ("all done!")

End Sub

When code has Sheet1 ... or Sheet2 ... in it, that is referring to the order of the worksheets. When you open a new workbook (nothing has been done to it yet), it will have Sheet1 / Sheet2 / Sheet3 to start with.
Those sheets are in the order of 1 - 2 - 3. Sheet1 (red word above) is referring to the first sheet on the left. For example: if the tabs were in the order of Sheet2 / Sheet1 / Sheet3 ..... as far as Excel is concerned, it see's
Sheet2 as the first sheet in the three because that is the order they are being displayed in the workbook. So ... in code ... in this specific case, Sheet1 is referring to the FIRST SHEET in the three, which is actually Sheet2.

(I tried to explain that in such a way that is clear. Hope it didn't confuse you).

Now, having said all of that ... let's do this.

Looking at the tabs from left to right ... what are the names ? It is important they be read left to right.

Let me give you an example: The left most tab is named "My Other Sheet" ... the middle tab is named "My First Sheet" ... the right most tab is named "Receipts"

Excel 'reads' these tabs as : My Other Sheet = Sheet1 ........ My First Sheet = Sheet2 ......... Receipts = Sheet3.

In a coded macro these sheets would be referred to as : Sheets("My Other Sheet") ..... Sheets("My First Sheet") ..... Sheets("Receipts")

Using that example as a guide, in the code, change the red SHEET1 to the appropriate order number of the sheet you are pasting the code.

[Logit]

Deleted duplicate post.

[Dark0Prince]

What is the name of the sheet you are attempting to insert the code ?
Look at the red Sheet1 name below ...

Option Explicit

Sub practice()
Dim k, i As Integer
Dim val As String
val = ""

For k = 1 To Sheet1.UsedRange.Columns.Count '<--- Change sheet name to match

For i = 2 To 25
If Cells(i, k).Value = "" Then
Cells(i, k).Value = val
ElseIf Cells(i, k).Value = "99" Or Cells(i, k).Value = 99 Then
Exit For
Else
val = Cells(i, k).Value
End If
Next i
Next k

MsgBox ("all done!")

End Sub

When code has Sheet1 ... or Sheet2 ... in it, that is referring to the order of the worksheets. When you open a new workbook (nothing has been done to it yet), it will have Sheet1 / Sheet2 / Sheet3 to start with.
Those sheets are in the order of 1 - 2 - 3. Sheet1 (red word above) is referring to the first sheet on the left. For example: if the tabs were in the order of Sheet2 / Sheet1 / Sheet3 ..... as far as Excel is concerned, it see's
Sheet2 as the first sheet in the three because that is the order they are being displayed in the workbook. So ... in code ... in this specific case, Sheet1 is referring to the FIRST SHEET in the three, which is actually Sheet2.

(I tried to explain that in such a way that is clear. Hope it didn't confuse you).

Now, having said all of that ... let's do this.

Looking at the tabs from left to right ... what are the names ? It is important they be read left to right.

Let me give you an example: The left most tab is named "My Other Sheet" ... the middle tab is named "My First Sheet" ... the right most tab is named "Receipts"

Excel 'reads' these tabs as : My Other Sheet = Sheet1 ........ My First Sheet = Sheet2 ......... Receipts = Sheet3.

In a coded macro these sheets would be referred to as : Sheets("My Other Sheet") ..... Sheets("My First Sheet") ..... Sheets("Receipts")

Using that example as a guide, in the code, change the red SHEET1 to the appropriate order number of the sheet you are pasting the code.

I'm posting my code in the personal workbook on sheet1 so I can use this code no matter what I open. so after I post it there I create a new workbook to test and it only does the first column. It's easy to duplicate what I'm doing I've gone to multiple computers and it's the same result. I can't save code on just that workbook because that defeats it's purpose.

On a side note I don't even use the second or third sheets to test because I know they won't work.

[Logit]

I believe you are wanting the macro code to do something other than what it is designed to do. However, it is confusing to me what the goal is.

The information in your posts, as I understand it, resulted in the macro provided.

If there is another way of describing the goal, it may make a difference. Or perhaps a fresh set of eyes on this thread would also assist.

This is not an attempt to question your or my abilities .... it's just that the written word doesn't always convey the intended message. You know what
your goal is - you can see it in front of you - but alas, I can't. So a different way of describing what needs to be done is in order.

[NoSparks]

I'm putting this in a personal work book

When code has Sheet1 ... or Sheet2 ... in it, that is referring to the order of the worksheets.

actually those are the sheets default Code Names, the order would be Sheet(1), Sheet(2), etc.
you can't use a sheets code name when referring to it in another workbook.

Try putting this into a standard module in your "personal work book". Change the workbook and sheet name to what you're actually working on.

Please Login or Register  to view this content.

@ Logit
Have a look at this page, down about half way, there's a heading
Pay Attention To Variables Declared With One Dim Statement

[protonLeah]

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

[Logit]

Hey NoSparks ... good to 'see' you again.

Thanks for the heads up. Still learning here.
Bad habit picked up from someone else who isn't aware either.

Good information.

[Dark0Prince]

Why do i need to refer this to anything other then activesheet. That code does work if i save the workbook exactly what's in the code, but i can't expect that to be a practical solution when opening a workbook and having to change my code each time. I don't have to make such accommodations for anything else I have written.

[NoSparks]

You don't, provided you have the right sheet active.

The issue is using Sheet1 in the previous codes.
Written like that it's the sheets vba code name which is limited to the workbook containing the code.

[xlnitwit]

The issue is using Sheet1 in the previous codes.
Written like that it's the sheets vba code name which is limited to the workbook containing the code.

Hi,

That is not absolutely correct. If you set a reference from one workbook to another, you may refer to the codenames of sheets in the referenced workbook, prefixed with the name of the project.