Data not pulling from secondary workbook

[DanSMT]

Hey all,

Im trying to pull data from a secondary workbook based on the information entered into an array of textboxes on a userform. Everything else appears to be working except when trying to pull the information over from the secondary workbook. Any ideas?

Private Sub testcb_Click()

Dim i2 As Long
For i2 = 1 To 20
If Me.Controls("RITB" & i2).Text = vbNullString Then
Else
Worksheets("fai").Copy after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Me.Controls("RITB" & i2).Text & " FAI"
ActiveSheet.Range("c4").Value = Me.Controls("RITB" & i2).Text
Dim source, target As Worksheet
Set target = Workbooks("New RI Form rev9 - Develop Mode - DO NOT USE.xlsb").Sheets("RI Log")

targetlastrow = target.Range("B" & target.Rows.Count).End(xlUp).Row

    For j = 2 To targetlastrow
        If target.Range("B" & j).Value = ActiveSheet.Range("c4").Value Then

        ActiveSheet.Range("A9").Value = "A"
        ActiveSheet.Range("B9").Value = target.Cells(rng.Row, 90).Value
        ActiveSheet.Range("C9").Value = target.Cells(rng.Row, 91).Value
        ActiveSheet.Range("D9").Value = target.Cells(rng.Row, 89).Value

        End If
    Next j

End If
Next

End Sub

 

[johnnyL]

Dim source, target As Worksheet
Set target = Workbooks("New RI Form rev9 - Develop Mode - DO NOT USE.xlsb").Sheets("RI Log")

Should probably be:

Dim source, target As Workbook
Set target = Workbooks("New RI Form rev9 - Develop Mode - DO NOT USE.xlsb")

Then dim the sheet separately

Dim TargetSheet as worksheet
Set TargetSheet = Sheets("RI Log")

Then adjust your code below that to acommodate the change

 

[DanSMT]

Target shows = Nothing

 

[DanSMT]

Target shows = Nothing

Also errors out saying subscript out of range

 

[johnnyL]

Is the Target workbook open?

 

[DanSMT]

Is the Target workbook open?

it wasn't, but i opened and tried again and got type mistmatch error

 

[johnnyL]

Post the code you have now and what line of code is giving that error?

 

[DanSMT]

Type Mismatch error on the large font line

Private Sub testcb_Click()

Dim i2 As Long
For i2 = 1 To 20
If Me.Controls("RITB" & i2).Text = vbNullString Then
Else
Worksheets("fai").Copy after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Me.Controls("RITB" & i2).Text & " FAI"
ActiveSheet.Range("c4").Value = Me.Controls("RITB" & i2).Text

Dim source, target As Worksheet
[SIZE=6][B]Set target = Workbooks("New RI Form rev9 - Develop Mode - DO NOT USE.xlsb")[/B][/SIZE]

targetlastrow = target.Range("B" & target.Rows.Count).End(xlUp).Row

    For j = 2 To targetlastrow
        If target.Range("B" & j).Value = ActiveSheet.Range("c4").Value Then

        ActiveSheet.Range("A9").Value = "A"
        ActiveSheet.Range("B9").Value = target.Cells(rng.Row, 90).Value
        ActiveSheet.Range("C9").Value = target.Cells(rng.Row, 91).Value
        ActiveSheet.Range("D9").Value = target.Cells(rng.Row, 89).Value

        End If
    Next j

End If
Next

End Sub

 

[DanSMT]

oops cant edit VBA code font apparently.

Line is

Set target = Workbooks("New RI Form rev9 - Develop Mode - DO NOT USE.xlsb")

 

[johnnyL]

You didn't make all of the changes I mentioned in post #2.