Help with VBA code

Michael Cavaggioni 1 Reputation point
2021-04-05T13:40:33.957+00:00

Hello all,
Need some help with VBA code for a userform.

Userform below, code as well - getting error message. Any help is super appreciated

84420-image.png

84430-image.png

0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Michael Taylor 45,986 Reputation points
    2021-04-05T14:43:27.73+00:00

    What error are you getting? The odd thing about the code you posted to me is that you have foreign characters in what looks like it should be double quoted strings.

    1. Call to ListObject
    2. What value of Find call
    3. Assignment to Value in CommandButton2_Click

    Just guessing that it should be an empty string "" instead.


  2. Michael Cavaggioni 1 Reputation point
    2021-04-05T15:40:25.103+00:00

    i dont know it either gives me the runtime error or sometimes a compile error Expected: list separator or )

    84434-image.png

    0 comments No comments

  3. Michael Taylor 45,986 Reputation points
    2021-04-05T16:15:30.67+00:00

    On the line just before it LookAt := x1Part has a dot at end, change to a comma.

    VBA is interpreted so it doesn't detect compiler errors until it gets to the line. Most likely the runtime error occurs before this line so you see that first. If no error occurs then you get the compiler error. That would tend to lead me to believe the rng value is Nothing.

    Before the Find call try adding this.

    MsgBox(rng, VbOK, "Range Value")
    

    If my VB is correct this will display the value of rng. If you see nothing in the message then it is empty and that is likely causing your issues. If it should never be empty then there is something wrong with the ListObjects call. If it is OK to be empty then you need to protect your code from it. Replace the MsgBox call with this.

    If rng Is Not Nothing Then
       ' Put all the code after this line up to the End Sub in here
    End If
    
    0 comments No comments

  4. tarun 1 Reputation point
    2022-05-05T09:17:20.887+00:00

    Hi, I am completely beginner for EXCEL VBA, here I have created a Excel work book having Three worksheets, where I would like to enter data in cells range "C9: C25" as 'ITEM NAME' and in cells range "E9:E25" as 'ITEM QUANTITY' in the “INVOICE” Sheet along with "SAVE" Button . When I press “SAVE” button on “INVOICE” Sheet, and then that data range from (C9:C25) as 'ITEM NAME' , (E9:E25) as 'ITEM QUANTITY' and cell value of 'G33' as 'TOTAL INVOICE AMOUNT' from "INVOICE SHEET"would be transfered to Column cells "D2:AL2" of "INVOICE LIST"sheet, to Cell no "D2","F2","H2","J2","L2","N2","P2","R2","T2","V2","X2", "Z2","AB2","AD2","AF2","AH2","AJ2" as 'ITEM NAME' and to Cell no "E2","G2","I2","K2","M2","O2","Q2","S2","U2","W2","Y2", "AA2","AC2","AE2","AG2","AI2","AK2" as 'ITEM QUANTITY' and to cell no "AL2" as 'TOTAL INVOICE AMOUNT' and contains of schedule cell of “INVOICE” Sheet would be clear except FORMULA. Then if I enter again data to Cell range "C9:C25" and Cell range "E9:E25" in my "INVOICE" Sheet for create new invoice and press the SAVE button on "INVOICE" Sheet I would need the contains of "INVOICE" Sheet would be transfer except FORMULA to "INVOICE LIST" Sheet to the next available blank row "D3:AK3", and “AL3”, and every time when I press the "SAVE Button" the event would be continue to happen as above frequently. but it not works. May I get help to correct the same I have Code which i wrote as below

    Sub SaveData()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim r As Long
    Application.ScreenUpdating = False
    Set ws = Worksheets("INVOICE")
    Set wt = Worksheets("INVOICELIST")
    ' Find first empty row in columns D:AL on sheet INVOICELIST
    r = wt.Range("D:AK").Find(What:="", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    wt.Range("D" & r).Resize(1, 17).Value = Application.Transpose(ws.Range("C9:C25"))
    r = wt.Range("D:AK").Find(What:="
    ", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    wt.Range("E" & r).Resize(1, 17).Value = Application.Transpose(ws.Range("E9:E25"))
    wt.Range("AL" & r).Value = ws.Range("G33").Value

    'Copy inv no
    

    Sheets("INVOICELIST").Range("B" & r).Value = Sheets("INVOICE").Range("G1").Value
    'Copy Date
    Sheets("INVOICELIST").Range("A" & r).Value = Sheets("INVOICE").Range("G2").Value
    'Copy Company Name
    Sheets("INVOICELIST").Range("C" & r).Value = Sheets("INVOICE").Range("D3").Value
    r = r + 1
    Application.ScreenUpdating = True
    End Sub