VBA Variable Row Selected In Wrong Sheet

A

AcornNut

Board Regular
Joined
Aug 19, 2014
Messages
51
Hello all,

I'm trying to populate an order form ("Supply Usage Form") using VBA. I have a command button that, when selected, loops through a location-based inventory sheet ("Location") and checks columns L & M for missing or expiring items. If any are found, it auto-populates the order form. Before moving onto the next row, it loops through the order form looking for duplicate items in column B. If it finds any, it goes to the first time that item is listed and then tally's the quantities in column H. Then it's supposed to select the duplicate row and clear it. However, every time it reaches this line of code, Rows(x).EntireRow.ClearContents", it selects the corresponding row in the inventory sheet, not the order form. How can I get it to stay on the order form? I've tried declaring my rows as "integer" and as "long", and every combination I can think of.

Thanks in advance!!!!!!

Here's my code...

Code: 
Sub fillorder1()
   Dim finalrow As Long
   Dim i As Integer
   Dim lastrow As Long
   Dim NextRow As Long
   Dim Flg As Boolean
   Dim FirstRow As Long
   Dim x As Long
   Dim y As Long
   Dim LR1 As Long

   Application.ScreenUpdating = False
   LR1 = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
   NextRow = 54
   lastrow = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row + 1
   finalrow = Sheets("Location").Range("B9").End(xlDown).Row

Flg = True
    For i = 9 To finalrow
        If Flg And lastrow > 53 Then
            Rows(NextRow).Insert
            Rows(53).Copy
            Rows(NextRow).PasteSpecial xlFormats
            lastrow = NextRow
            NextRow = NextRow + 1
        End If
        Flg = False
        If (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) = "") Then
                Sheets("Location").Cells(i, 1).Copy
                    Sheets("Supply Usage Form").Range("B" & lastrow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
                Sheets("Location").Cells(i, 2).Copy
                    Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
                Sheets("Location").Cells(i, 12).Copy
                    Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
                lastrow = lastrow + 1
                Flg = True
                LR1 = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
                For x = LR1 To 24 Step -1
                    For y = 24 To LR1
                        If Sheets("Supply Usage Form").Cells(y, 4).Value = Sheets("Supply Usage Form").Cells(x, 4).Value And x > y Then
                            Sheets("Supply Usage Form").Cells(y, 8).Value = Sheets("Location").Cells(i, 2).Value + Sheets("Supply Usage Form").Cells(x, 8).Value
                            Rows(x).EntireRow.ClearContents
                        Exit For
                        End If
                    Next y
                Next x


        ElseIf (Sheets("Location").Cells(i, 12) = "") And (Sheets("Location").Cells(i, 13) <> "") Then
            Sheets("Location").Cells(i, 1).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 2).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 13).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
            lastrow = lastrow + 1
            Flg = True
            LR1 = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
                For x = LR1 To 24 Step -1
                    For y = 24 To LR1
                        If Sheets("Supply Usage Form").Cells(y, 4).Value = Sheets("Supply Usage Form").Cells(x, 4).Value And x > y Then
                            Sheets("Supply Usage Form").Cells(y, 9).Value = Sheets("Location").Cells(i, 2).Value + Sheets("Supply Usage Form").Cells(x, 9).Value
                            Rows(x).EntireRow.Select
                        Exit For
                        End If
                    Next y
                Next x
                
        ElseIf (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) <> "") Then
            Sheets("Location").Cells(i, 1).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 2).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 12).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 13).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
            lastrow = lastrow + 1
            Flg = True
            LR1 = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
                For x = LR1 To 24 Step -1
                    For y = 24 To LR1
                        If Sheets("Supply Usage Form").Cells(y, 4).Value = Sheets("Supply Usage Form").Cells(x, 4).Value And x > y Then
                            Sheets("Supply Usage Form").Cells(y, 8).Value = Sheets("Location").Cells(i, 2).Value + Sheets("Supply Usage Form").Cells(x, 8).Value
                            Sheets("Supply Usage Form").Cells(y, 9).Value = Sheets("Location").Cells(i, 2).Value + Sheets("Supply Usage Form").Cells(x, 9).Value
                            Rows(x).EntireRow.ClearContents
                        Exit For
                        End If
                    Next y
                Next x
        End If
    Next i
End Sub
 
Last edited by a moderator:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Code: 
Sheets("Supply Usage Form").Rows(x).EntireRow.ClearContents
 
Upvote 0
Before filling out the "Supply Usage Form", you should check if the item already exists, if it exists then tally's the quantities in column H.
That way you would not have to clear row.
 
Upvote 0
AcornNut said:
Thanks, but when I write it like that, I get a 1004 runtime error.
Click to expand...

First of all I agree with the method DanteAmor has stated in post number 4 but as for your error what do you get if you change

Code: 
Sheets("Supply Usage Form").Rows(x).EntireRow.ClearContents
to
Code: 
Sheets("Supply Usage Form").Rows(x).ClearContents
?
 
Upvote 0
Mark858, thank you for your reply. I agree with the method as well. That was actually the first method I tried. Then I tried it as a range, and find as cells, etc. Nothing seemed to work. But as I was just now going back and re-trying the code you suggested, I realized I had 3 windows open, one for the inventory, one for the order form, and one for the code. When I was running the code it would switch the other windows to the order form so I couldn't follow the process as I was debugging. So to keep it from doing that, I was selecting the inventory sheet window before selecting the code window to run the code. This, I'm guessing, made the inventory sheet the active sheet and is why the code was selecting the row there (although I do not know why it kept doing that when I specifically stated it for the order form). When I have only the order form window open, the code selects the right row in the right sheet.

DanteAmor, I would LOVE it if the code did that! I tried but I could not get it right to do it that way. The code would run a WHOLE lot quicker and cleaner. However, the code as I have it was the best I could come up with over the past week of trying. Any suggestions on how to do that?

Here's what I have now...

Code: 
Sub fillorder1()
   Dim finalrow As Long
   Dim i As Long
   Dim lastrow As Long, NextRow As Long
   Dim Flg As Boolean
   Dim LR As Long
   Dim FirstRow As Long
   Dim x As Long
   Dim y As Long

Application.ScreenUpdating = False



    LR = Sheets("Supply Usage Form").Range("B24").Row
    LR1 = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
    NextRow = 54
    lastrow = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row + 1
    finalrow = Sheets("Location").Range("B9").End(xlDown).Row

Flg = True
    For i = 9 To finalrow
        If Flg And lastrow > 53 Then
            Rows(NextRow).Insert
            Rows(53).Copy
            Rows(NextRow).PasteSpecial xlFormats
            lastrow = NextRow
            NextRow = NextRow + 1
        End If
        Flg = False
        If (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) = "") Then
                Sheets("Location").Cells(i, 1).Copy
                    Sheets("Supply Usage Form").Range("B" & lastrow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
                Sheets("Location").Cells(i, 2).Copy
                    Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
                Sheets("Location").Cells(i, 12).Copy
                    Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
                lastrow = lastrow + 1
                Flg = True
                LR = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
                For x = LR To 24 Step -1
                    For y = 24 To LR
                        If Sheets("Supply Usage Form").Cells(y, 4).Value = Sheets("Supply Usage Form").Cells(x, 4).Value And x > y Then
                            Sheets("Supply Usage Form").Cells(y, 8).Value = Sheets("Supply Usage Form").Cells(y, 8).Value + Sheets("Supply Usage Form").Cells(x, 8).Value
                            Rows(x).EntireRow.ClearContents
                            lastrow = lastrow - 1
                        Exit For
                        End If
                    Next y
                Next x


        ElseIf (Sheets("Location").Cells(i, 12) = "") And (Sheets("Location").Cells(i, 13) <> "") Then
            Sheets("Location").Cells(i, 1).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 2).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 1).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 13).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
            lastrow = lastrow + 1
            Flg = True
            LR = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
                For x = LR To 24 Step -1
                    For y = 24 To LR
                        If Sheets("Supply Usage Form").Cells(y, 4).Value = Sheets("Supply Usage Form").Cells(x, 4).Value And x > y Then
                            Sheets("Supply Usage Form").Cells(y, 9).Value = Sheets("Supply Usage Form").Cells(y, 9).Value + Sheets("Supply Usage Form").Cells(x, 9).Value
                            Rows(x).EntireRow.ClearContents
                            lastrow = lastrow - 1
                        Exit For
                        End If
                    Next y
                Next x
                
        ElseIf (Sheets("Location").Cells(i, 12) <> "") And (Sheets("Location").Cells(i, 13) <> "") Then
            Sheets("Location").Cells(i, 1).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(, 0).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 2).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 12).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 5).PasteSpecial xlPasteValuesAndNumberFormats
            Sheets("Location").Cells(i, 13).Copy
                Sheets("Supply Usage Form").Range("B" & lastrow).Offset(0, 6).PasteSpecial xlPasteValuesAndNumberFormats
            lastrow = lastrow + 1
            Flg = True
            LR = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row
                For x = LR To 24 Step -1
                    For y = 24 To LR
                        If Sheets("Supply Usage Form").Cells(y, 4).Value = Sheets("Supply Usage Form").Cells(x, 4).Value And x > y Then
                            Sheets("Supply Usage Form").Cells(y, 8).Value = Sheets("Supply Usage Form").Cells(y, 8).Value + Sheets("Supply Usage Form").Cells(x, 8).Value
                            Sheets("Supply Usage Form").Cells(y, 9).Value = Sheets("Supply Usage Form").Cells(y, 9).Value + Sheets("Supply Usage Form").Cells(x, 9).Value
                            Rows(x).EntireRow.ClearContents
                            lastrow = lastrow - 1
                        Exit For
                        End If
                    Next y
                Next x
        End If
    Next i


    lastrow = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row + 1
        If Sheets("Supply Usage Form").Range("B" & lastrow).Row > 53 Then
            Rows(lastrow).Delete
        End If


    FirstRow = Sheets("Supply Usage Form").Range("B22").End(xlDown).Row + 1
    lastrow = 53


    Sheets("Supply Usage Form").Range(FirstRow & ":" & lastrow).ClearContents

   Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
AcornNut said:
DanteAmor, I would LOVE it if the code did that! I tried but I could not get it right to do it that way. The code would run a WHOLE lot quicker and cleaner. However, the code as I have it was the best I could come up with over the past week of trying. Any suggestions on how to do that?
Click to expand...

If you explain to me with examples step by step what you want to do, I gladly make the new code.
 
Upvote 0
AcornNut said:
Mark858, thank you for your reply....
Here's what I have now...
Click to expand...

So are you saying that you still got the 1004 error with the amended code I posted as you haven't mentioned it and it isn't being used in the last code you posted?

If yes, are there any merged cells on the sheet? and is the sheet protected?
 
Last edited:
Upvote 0
AcornNut said:
Thanks, but when I write it like that, I get a 1004 runtime error.
Click to expand...

I cannot duplicate the error. The code works for me and clears the contents of the appropriate row on the appropriate sheet. Check the spelling amd as MARK858 pointed out, you don't really need the '.EntireRow' proiperty as part of the statement, but it shouldn't make any difference in execution.

Your entire could could be more streamlined by using a couple of object variables and With statements, but it worked for me as is.
 
Last edited:
Upvote 0
Sorry. Yes. I still got the 1004 error with the amended code you provided. However, while debugging, I was opening 3 separate windows: 1) Inventory Sheet, 2) Order Form, 3) Code Sheet. I did this so I could look between the inventory and order forms and follow/debug the code line by line. When I'd run the code, sometimes it would automatically switch the window that had the inventory in it to the order form, so both windows would show the order form. To avoid this and follow the code, I had to select the window that had the inventory in it last before returning to the code editor. When I'd do that and run the code, both windows stayed as I wanted them to (one with the inventory and the other with the order form). But that's when it would either select the wrong row, or I'd get the 1004 error with the amended code. When I run the code from the order form (with only one window open), it works perfectly. I'm guessing that's what the problem was...selecting the window with the inventory activated may have made the code revert to that "active" window. I don't know, though. Just a guess. But both of your and DanteAmor's codes worked without error.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Modifying code for message box on userform based on selected combobox
Replies
0
Views
40
Ali M
A
T
  • Solved
Populate Worksheet Rows Based on Userform Checkbox selection
2
Replies
11
Views
178
TellM1955
T
C
  • Question Question
Using pattern searches with a variable to find the correct values
2
Replies
10
Views
265
chazrab
C
O
  • Solved
VBA - floating photo
Replies
3
Views
521
RoryA
RoryA
U
  • Solved
Rewrite Code as a Sub to work on Active Sheet
Replies
2
Views
51
Skyybot
Skyybot

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top