I am new on VBA and need help

[KingGoku]

Hi there,

I am getting error on following code. can't seem to get find last row function working. i have tried last row as range and long. please help.

 

[KingGoku]

I can't understand why the following erroring? when I am trying to enter data from userform to excel.

Sub Button()

Dim i As Integer
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Dim LastRow As Long

Dim wsName As String





wsName = FormSelectSht.ComboBoxMachines.Value

For Each ws In wb.Sheets
    If ws.Name = wsName Then ws.Activate
   

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
   
   
   
   
Next



'énter data into active sheet
'ws.Range("A" & LastRow) = Date

With ws
    .Range("B" & LastRow).Value = FormSelectSht.ComboBoxProduct.Value
     '  .Cells(LastRow, 2).Value = FormSelectSht.ComboBoxProduct.Value
    '.Cells("B" & LastRow).Value = FormSelectSht.ComboBoxProduct.Value
'ws.Range("C" & LastRow) = juliandt
'ws.Range("D" & LastRow).Value = FormSelectSht.TextBoxOperator1.Value
'ws.Range("E" & LastRow).Value = FormSelectSht.TextBoxOperator2.Value
'ws.Range("F" & LastRow).Value = FormSelectSht.TextBoxLot.Value
End With


End Sub

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags to your last post for you this time.

Also, when asking the forum about vba errors, as well as telling us which line caused the problem you should also tell us exactly what the error message was.

In this case it is obvious though: The line before the red one is With ws
You have declared ws as Worksheet but you have not set it to any particular worksheet so when the vba gets to the red line it does not know which worksheet to look for Range("B" & LastRow) on.

I also don't know why you are looping through all the worksheets, even continuing to do so after you come across the one called wsName.
I don't know your exact goal and I don't have your form to work with but I'm guessing that something like this might be what you are trying to do.
Test with a copy of your workbook.

Sub Button()
  Dim ws As Worksheet
  Dim LastRow As Long
  Dim wsName As String

  wsName = FormSelectSht.ComboBoxMachines.Value
  Sheets(wsName).Activate
  LastRow = Range("A" & Rows.Count).End(xlUp).Row
   
  'énter data into active sheet
  Range("B" & LastRow).Value = FormSelectSht.ComboBoxProduct.Value

End Sub

 

[KingGoku]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags to your last post for you this time.

Also, when asking the forum about vba errors, as well as telling us which line caused the problem you should also tell us exactly what the error message was.

In this case it is obvious though: The line before the red one is With ws
You have declared ws as Worksheet but you have not set it to any particular worksheet so when the vba gets to the red line it does not know which worksheet to look for Range("B" & LastRow) on.

I also don't know why you are looping through all the worksheets, even continuing to do so after you come across the one called wsName.
I don't know your exact goal and I don't have your form to work with but I'm guessing that something like this might be what you are trying to do.
Test with a copy of your workbook.

Sub Button()
  Dim ws As Worksheet
  Dim LastRow As Long
  Dim wsName As String

  wsName = FormSelectSht.ComboBoxMachines.Value
  Sheets(wsName).Activate
  LastRow = Range("A" & Rows.Count).End(xlUp).Row
  
  'énter data into active sheet
  Range("B" & LastRow).Value = FormSelectSht.ComboBoxProduct.Value

End Sub

Hi Peter,

Thank you for your help and notes. i wish i could just email you the excel sheet. for some reason i can't download mini sheet.

 

[Peter_SSs]

for some reason i can't download mini sheet.

At what point of the instructions to you run into a problem and what exactly is that problem?
Not sure that a mini sheet will help here though as the form will not come with the mini sheet.

Did you try that last code?

 

[KingGoku]

At what point of the instructions to you run into a problem and what exactly is that problem?
Not sure that a mini sheet will help here though as the form will not come with the mini sheet.

Did you try that last code?

yes i tried the code it still giving me run time error 91.
"object variable or with block variable not set."

 

[Peter_SSs]

yes i tried the code it still giving me run time error 91.
"object variable or with block variable not set."

Unless you changed the code or combined it with other code I cannot see how what I posted could produce that error.

Also remember..

... telling us which line caused the problem you should also tell us exactly what the error message was.

This time you gave us the second part but not the first.

 

[bebo021999]

Unless you changed the code or combined it with other code I cannot see how what I posted could produce that error.

wsName = FormSelectSht.ComboBoxMachines.Value
Sheets(wsName).Activate

Hi Pete, you latest code works if that sheet "wsname" was exists.

For Each ws In wb.Sheets
If ws.Name = wsName Then ws.Activate
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
Next
With ws
.Range("B" & LastRow).Value = FormSelectSht.ComboBoxProduct.Value

Hi KingGoku,
After the For..Next loop is completed, the last "ws" (worksheet) will always be the last sheet, regardless of whether your active sheet is different.
Your code set value to last cell of column B of last sheet, not activesheet

it should be:

Dim c as boolean
For Each ws In wb.Sheets
    If ws.Name = wsName Then 
          c= true      ' to confirm that wsName was exists     
          ws.Activate
     end if 
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
 Next
If c then Range("B" & LastRow).Value = FormSelectSht.ComboBoxProduct.Value

 

[Peter_SSs]

Hi Pete, you latest code works if that sheet "wsname" was exists.

Yes, and if that sheet does not exist you get a different error message to the one that was reported.