Maxicus
02-19-2018, 12:06 AM
Hi
I am having a problem with one of my codes not working at 100%. and need some help figuring out why.
I have a userform, that if i click on a button, it runs a sub function to call data from a specific place in the worksheet. (Sheet7)
the code works perfect if i have that sheet open, but it the worksheet is perhaps on a different sheet like Sheet6, the data is not transferred.
I will Show the two codes that i am using.
Firstly a button that triggers the sub function
Private Sub cmdSearch_Click()
Sheet7.Range("H2") = Me.Reg1.Value
finddata
'declare the variables
Dim FindRow
Dim i As Integer
Dim cRow As String
'error block
On Error GoTo errHandler:
'find the row with the data
cRow = Me.Reg1.Value
Set FindRow = Sheet2.Range("G:G").Find(What:=cRow, LookIn:=xlValues)
'add the values to the userform
Me.Reg1.Value = FindRow
Me.Reg2.Value = FindRow.Offset(0, -1)
Me.Reg3.Value = FindRow.Offset(0, 16)
Me.Reg4.Value = FindRow.Offset(0, 17)
Me.PPE1.Value = FindRow.Offset(0, 22)
Me.PPE2.Value = FindRow.Offset(0, 23)
Me.PPE3.Value = FindRow.Offset(0, 24)
Me.PPE4.Value = FindRow.Offset(0, 25)
'error block
On Error GoTo 0
Exit SuberrHandler:MsgBox "Error! Payroll ID Does Not Exist."
End Sub
and the sub function (Finddata)
Sub finddata()
Dim ID As String
Dim finalrow As IntegerDim
i As Integer
Sheet7.Range("J2:N101").ClearContents
ID = Sheet7.Range("H2").Text
finalrow = Sheet7.Range("A90000").End(xlUp).Row
For i = 2 To finalrow
If Cells(i, 1) = ID Then
Range(Cells(i, 2), Cells(i, 5)).Copy
Range("J100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next i
lstSearch1.RowSource = Sheet7.Range("PPE").Address(external:=True)
End Sub
I am only doing VBA coding for 2 weeks, so my knowledge on it is very limited.
I am having a problem with one of my codes not working at 100%. and need some help figuring out why.
I have a userform, that if i click on a button, it runs a sub function to call data from a specific place in the worksheet. (Sheet7)
the code works perfect if i have that sheet open, but it the worksheet is perhaps on a different sheet like Sheet6, the data is not transferred.
I will Show the two codes that i am using.
Firstly a button that triggers the sub function
Private Sub cmdSearch_Click()
Sheet7.Range("H2") = Me.Reg1.Value
finddata
'declare the variables
Dim FindRow
Dim i As Integer
Dim cRow As String
'error block
On Error GoTo errHandler:
'find the row with the data
cRow = Me.Reg1.Value
Set FindRow = Sheet2.Range("G:G").Find(What:=cRow, LookIn:=xlValues)
'add the values to the userform
Me.Reg1.Value = FindRow
Me.Reg2.Value = FindRow.Offset(0, -1)
Me.Reg3.Value = FindRow.Offset(0, 16)
Me.Reg4.Value = FindRow.Offset(0, 17)
Me.PPE1.Value = FindRow.Offset(0, 22)
Me.PPE2.Value = FindRow.Offset(0, 23)
Me.PPE3.Value = FindRow.Offset(0, 24)
Me.PPE4.Value = FindRow.Offset(0, 25)
'error block
On Error GoTo 0
Exit SuberrHandler:MsgBox "Error! Payroll ID Does Not Exist."
End Sub
and the sub function (Finddata)
Sub finddata()
Dim ID As String
Dim finalrow As IntegerDim
i As Integer
Sheet7.Range("J2:N101").ClearContents
ID = Sheet7.Range("H2").Text
finalrow = Sheet7.Range("A90000").End(xlUp).Row
For i = 2 To finalrow
If Cells(i, 1) = ID Then
Range(Cells(i, 2), Cells(i, 5)).Copy
Range("J100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next i
lstSearch1.RowSource = Sheet7.Range("PPE").Address(external:=True)
End Sub
I am only doing VBA coding for 2 weeks, so my knowledge on it is very limited.