URENT!!! HELP!! Excel VBA. Been Stuck for 4 hours...

[dhs911230]

Hi.

This is probably very hard to follow, but I'll try my best because I am very desperate for help.

Counts = number of columns I need to copy.
BLANK = a variable that stores a certain integer such as 7,8,or 9.
Height = How long I want the range to be. (This length is in row direction.)

I am trying to find BLANK in a row, first. Then, I will copy a range of cells below the BLANK cell, and paste it onto another sheet. the range will have the length of height (in y direction).

I've been running the code below, and kept getting RunTimeError 1004:application defined object defined error".

Please Help!

* I know this is a very confusing description but I will GREATLY appreciate your help. * Below is only a part of a huge code, so It does not have "sub" or "dim".

My Code is --------------------------------------------------------------

Sheets(3).Select
ActiveSheet.Cells(7, 2 + Width).Select
m = 0
While m <= Counts
If Selection.Value = BLANK Then
Selection.Offset(1, 0).Select
x = ActiveCell.Row
y = ActiveCell.Column
Sheets(3).Range(Cells(8, y), Cells((7 + Height), y)).Copy
Sheets(1).Select
Cells(17, 4 + m).Select
ActiveSheet.Paste
m = m + 1
Sheets(3).Select
ActiveSheet.Cells(x, y).Select
Selection.Offset(-1, 1).Select
End If
If Selection.Value <> BLANK Then
x = ActiveCell.Row
y = ActiveCell.Column
y = y + 1
Sheets(3).Select
Sheets(3).Cells(x, y).Select
Else
m = m
End If
Wend











THANKS!

 

[B540Glenn]

Question: How is BLANK defined?

 

[dhs911230]

So, Earlier in the code, I ask the user to input a number value for BLANK.
So,"BLANK = input("" )" like this.

 

[Joe4]

But how have you declared BLANK?
You should have a statement explicitly defining the data type, i.e.

Dim BLANK as Integer

If it is treated like a String, then your comparisons may not working properly without modifying your code some.

 

[dhs911230]

I did declare BLANK.
And if that was the problem, should it be highlighting BLANK? Instead, it's highlighting "Sheets(3).Cells(x, y).Select"

 

[Joe4]

And if that was the problem, should it be highlighting BLANK? Instead, it's highlighting "Sheets(3).Cells(x, y).Select"

Good point. I was just continuing on with the previous line of questioning, but going back to your original post, let's look at that.

Try adding this line of code just before that line:

MsgBox "x equals " & x & vbCrLf & "y equals " & y

and then try running your code and see what that message box says the values for x and y are at that time.
That may shed some light on the issue that it is having.

Now, take a look at the cell address that is, and make sure it is valid (you haven't exceeded the maximum number of columns), and that the cell is in a visible range that can be selected (i.e. not in a hidden area).

 

[JackDanIce]

I think the problem with the line you've indicated is you can't select a cell like that on a non-activesheet so it's not correct syntax.

Also, in your code you have:

If Selection.Value = BLANK
...
End If

If Selection.Value <> Blank
...
Else
m = m
End if


Why not use an ELSE and combine both parts:

If Selection.Value = Blank
...
Else
...
End If

Selection.Value can only equal BLANK or NOT equal BLANK so why do a second test to see if it's not equal to blank and then set m=m?

Try this code as an alternative:

Sheets(3).Select
ActiveSheet.Cells(7, 2 + Width).Select

m = 0

While m <= Counts
    If Selection.Value = BLANK Then
        Selection.Offset(1, 0).Select
        x = ActiveCell.Row
        y = ActiveCell.Column
        Sheets(3).Range(Cells(8, y), Cells((7 + Height), y)).Copy
        Sheets(1).Select
        Cells(17, 4 + m).Select
        ActiveSheet.Paste
        m = m + 1
        Sheets(3).Select
        Cells(x, y).Offset(-1, 1).Select
    Else
        x = ActiveCell.Row
        y = ActiveCell.Column
        y = y + 1
        Sheets(3).Select
        Cells(x, y).Select
    End If
Wend

 

[dhs911230]

Good point. I was just continuing on with the previous line of questioning, but going back to your original post, let's look at that.

Try adding this line of code just before that line:

MsgBox "x equals " & x & vbCrLf & "y equals " & y

and then try running your code and see what that message box says the values for x and y are at that time.
That may shed some light on the issue that it is having.

Now, take a look at the cell address that is, and make sure it is valid (you haven't exceeded the maximum number of columns), and that the cell is in a visible range that can be selected (i.e. not in a hidden area).

Thanks so much! I think It worked! The problem was that The
"y=y+1" part was going on until infinity. So, I set a limit to the while loop to limit the y value. Thanks!