question

MichaelCavaggioni-8875 avatar image
0 Votes"
MichaelCavaggioni-8875 asked cooldadtx commented

Help with VBA code

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


office-vba-dev
image.png (2.9 KiB)
image.png (21.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered MichaelCavaggioni-8875 commented

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.



· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thank you @cooldadtx

for some reason those quotes got shifter to strange character when screen shot was done but in the code those are double quotes - error message :: Run time error "424" - object required

0 Votes 0 ·
cooldadtx avatar image cooldadtx MichaelCavaggioni-8875 ·

Which line is it failing on?

0 Votes 0 ·
cooldadtx avatar image cooldadtx MichaelCavaggioni-8875 ·

My gut instinct is that the Find call is not finding what you're looking for so it returns Nothing. Then when calling to set values later it fails. Could also be the .Parent call for a cell with no parent but it would depend upon the spreadsheet.

Have you tried setting a breakpoint in the VBA editor on the Find call? When the command executes you can then step through and see where the Nothing is coming from.

0 Votes 0 ·

im relatively new to coding so most of this is kind of above me...lol...hopefully not for long

how and where do i go about adding a break?

0 Votes 0 ·
MichaelCavaggioni-8875 avatar image
0 Votes"
MichaelCavaggioni-8875 answered

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

84434-image.png



image.png (614 B)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

tarun-9885 avatar image
0 Votes"
tarun-9885 answered cooldadtx commented

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Please do not respond to someone else's question with your own. Create your own question in the forums and someone will try to help you.

0 Votes 0 ·