VBA to Move Data Based on Criteria

[Excel1991]

Hello,

I am a relatively modest Excel user who i just breaking into VBA. I was hoping some of the members of this forumn could help me with a project that I am working on.

Basically I have a master list of data (sheet one). On this list column A ("Quantity") is empty, and columns B through G have data for maufacturer, part type, price, etc.


I am essentiallty trying to write a code so that if someone were to put a value in Column A (Quanitity), it would generate on Sheet2.

Sheet2 is set up as an invoice, so it will have logos and other stuff at the top rows. Due to this, I do not want the data to start in cell A1. I would likely need to start it at lets say, cell A10.

I had written a code that seemed to work but it was rather buggy. I am having trouble getting the cells to generate at A10 on the second tab. Is there any way to define the range I want the cells to go into on Sheet2?

Additionally, I would like to know how easy it is to disrupt the code? For instance, if I have the data transferring to row A10 on sheet2, and someone later deletes, lets say Row2, would this mess up the VBA code/formula?

 

[Excel1991]

Hey Fluff,


I have run into another small issue regarding this project.If you enter a value on the quantity column on sheet1, it generates that row over tosheet2. This part is working great. However,because I am essentially creating an invoice, I added a “total” row so that I cansum up the values being brought over onto sheet2


This formula works until I revise/delete the cells onsheet1. As I delete the quantities, so that I can make changes or whatever, iterased the values on sheet2 like it is supposed to. However, it is alsodeleting a portion of my sum range, which is effectively shortening my formularange. So when I add more values on sheet 2, the sum formula is to short topick them all up. This is obviously easily fixed if you just rerun the sumfunction, but I would rather it be automatic.


Is it possible to make a sum function and lock it to thatspecified range? I tried to lock the range via “$” but it is not effective asthe code is clearing the lines.

 

[Fluff]

Add this line

      Fnd.EntireRow.Delete
      Sheets("sheet2").Rows(40).Insert
      [COLOR=#0000ff]Sheets("sheet2").Range("C41").Formula = "=sum(A10:A40)"
[/COLOR]

Change range & formula to suit

 

[Excel1991]

it does not appear to be working for me, although it is likely I could have input it incorrectly. Should this replace the previous line in the code

 ElseIf Target.Value = "" Then
      Fnd.EntireRow.Delete
      Sheets("Proposal").Rows(39).Insert

I have tried this but it does not appear to be working

 

[Fluff]

It does not replace any code, You just need to add it as shown.

 

[Excel1991]

I keep getting an error message as the “debug” functionhighlights the “fnd.EntireRow.Delete” portion of the code. If I erase this part of the code, and theninput a quantity on me sheet, the total will generate at line 41, but it alsoputs a total at line 42. As I add more quantities the total columns keep goingdown (a total at row 41, a total at row 42, a total at row 43)

End If
     Fnd.EntireRow.Delete
   Sheets("Proposal").Rows(40).Insert
      Sheets("Proposal").Range("C41").Formula = "=sum(H10:H140)"
End Sub

 

[Fluff]

What error message do you get?

 

[Excel1991]

"Run-time error '91':

Object variable or Wit block variable not set"

If I hit debug, it highlights the fnd.entirerow.delete



In this case I inserted the code after the last End If. not sure if that matters

 

[Fluff]

Can you post the entire code you are currently using

 

[Excel1991]

yes, they are unique

 

[Excel1991]

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Lr As Long
   Dim Fnd As Range
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column > 1 Then Exit Sub
   Set Fnd = Sheets("Proposal").Range("B:B").Find(Target.Offset(, 1).Value, , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then
      Lr = Sheets("Proposal").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      If Lr < 10 Then
         Lr = 10
      ElseIf Lr > 39 Then
         MsgBox "Too many lines"
         Exit Sub
      End If
      Sheets("Proposal").Range("A" & Lr).Resize(, 10).Value = Target.Resize(, 10).Value
   ElseIf Target.Value = "" Then
      Fnd.EntireRow.Delete
      Sheets("Proposal").Rows(39).Insert
   Else
      Fnd.Offset(, -1).Value = Target.Value
   End If
End Sub

this is before I added the sum formula code