VBA: Find cells that are not empty and copy to Textbox

[FrankRechter]

Hi

I am very new to VBA and need some advice.

I have a table where in Column F only a few cells are filled. I am looking for a code that can find all filled cells from F2 till F59 and copy them to a textbox or label in a userform. Somehow all other tips O found online did not work.
So far the code looks like this (I know, it's not much). The userform opens the excel sheet, and then i want to find and copy all filled cells.

Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
Workbooks.Open ("S:\Projects\SNDG Datenbank\Partner SNDG Test.xlsx")


ActiveWorkbook.Close (False)
End Sub

 

[John Davis]

Hi

I am very new to VBA and need some advice.

I have a table where in Column F only a few cells are filled. I am looking for a code that can find all filled cells from F2 till F59 and copy them to a textbox or label in a userform. Somehow all other tips O found online did not work.
So far the code looks like this (I know, it's not much). The userform opens the excel sheet, and then i want to find and copy all filled cells.

Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
Workbooks.Open ("S:\Projects\SNDG Datenbank\Partner SNDG Test.xlsx")


ActiveWorkbook.Close (False)
End Sub

Untested, try on a copy first, but does this help or give you some ideas?

Sub FrankRechter()
Dim lr As Long
Dim x As Range
Dim y As Range
Dim txtbox1 As TextBox
Dim startPos As Integer


With Range("F2:F59")

    .AutoFilter Field:=1, Criteria1:="<>"
    .SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A2")
    .AutoFilter
    .AutoFilter
    
End With

Sheets("Sheet2").Activate


lr = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

Set x = Range("A2:A" & lr)

For Each y In x

    txtbox1.Characters(Start:=1, Length:=Len(y.Value)).Text = y.Value & Chr(10)
    startPos = startPos + Len(y.Value) + 1

Next y



End Sub

 

[FrankRechter]

Thanks, yes I partly figured out how this works. However, it gives me the error message "Run time error '9' Subscript out of range".
I'm not sure what is causing the problem. Do I need to adjust the Textbox in case the formula finds too many cells that it would copy?

 

[vmser]

Thanks, yes I partly figured out how this works. However, it gives me the error message "Run time error '9' Subscript out of range".
I'm not sure what is causing the problem. Do I need to adjust the Textbox in case the formula finds too many cells that it would copy?

Which line of code gets highlighted when you get the error?
If your sheets do not hold a sheet called 'Sheet2' (by example due to using another language version) in index 'Sheet2' for the worksheet is out of range.

 

[FrankRechter]

Ok, this solves the first problem. But now I get a different error message: Run time error 91: Object variable or with block variable not set.

The line that gets highlighted is the one that opens this userform in the first place (UserForm2.Show) when clicking a button on the previous userform.

 

[vmser]

Ok, this solves the first problem. But now I get a different error message: Run time error 91: Object variable or with block variable not set.

The line that gets highlighted is the one that opens this userform in the first place (UserForm2.Show) when clicking a button on the previous userform.

You might want to post the code for that bit? Hard to see what happens when you don't see anything...

 

[FrankRechter]

Ok, sorry for that

Private Sub CommandButton4_Click()
UserForm2.Show
Application.ScreenUpdating = False
Workbooks.Open ("S:\Projects\SNDG Datenbank\Partner SNDG Test.xlsx") Range("F1") = TextBox2
ActiveWorkbook.Close (True)
End Sub

So when clicking on the button, the excel file is opened and F1 is filled with whatever number is in TextBox2. This then has an effect on which cells are empty and which are not. I can see in the file that the code works so far. The cells are found and copied to Sheet2. Only the part that copies the cells from sheet2 to the textbox (in Userform2) doesn't work.
Thanks for your help

 

[vmser]

Ok, sorry for that

Private Sub CommandButton4_Click()
UserForm2.Show
Application.ScreenUpdating = False
Workbooks.Open ("S:\Projects\SNDG Datenbank\Partner SNDG Test.xlsx") Range("F1") = TextBox2
ActiveWorkbook.Close (True)
End Sub

So when clicking on the button, the excel file is opened and F1 is filled with whatever number is in TextBox2. This then has an effect on which cells are empty and which are not. I can see in the file that the code works so far. The cells are found and copied to Sheet2. Only the part that copies the cells from sheet2 to the textbox (in Userform2) doesn't work.
Thanks for your help

Replace this:
Workbooks.Open ("S:\Projects\SNDG Datenbank\Partner SNDG Test.xlsx") Range("F1") = TextBox2

By this:
Workbooks.Open ("S:\Projects\SNDG Datenbank\Partner SNDG Test.xlsx")
ActiveWorkbook.Range("F1").Value = TextBox2.Text

To take what is in a textbox, acces the .Text property.
For the Range, you are trying to change the .Value attribute.

Also, it'd probably be wise that if you disable ScreenUpdating, that you make it work again by the end of the routine or you will not have your screen update..

 

[FrankRechter]

But this is the part that did work before. Now i get the message "Object doesn't support this property and method" and the line
ActiveWorkbook.Range("F1").Value = TextBox2.Text
is highlighted