Data not pulling from secondary workbook

D

DanSMT

Board Regular
Joined
Sep 13, 2019
Messages
203
Office Version
  1. 2013
Platform
  1. Windows
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?

VBA Code: 
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
 
Sort by date Sort by votes
Noticed that after I sent. The code runs, but doesnt pull the data over from the other sheet yet.

I will work on this again in the morning.

Code attached for reference.

VBA Code: 
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 target As Workbook
Set target = Workbooks("New RI Form rev9 - Develop Mode - DO NOT USE.xlsb")

Dim targetsheet As Worksheet
Set targetsheet = target.Sheets("RI Log")

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

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

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

        End If
    Next j

End If
Next

End Sub

Thanks for your help!
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
VBA Code: 
        ActiveSheet.Range("B9").Value = targetsheet.Cells(rng.Row, 90).Value
        ActiveSheet.Range("C9").Value = targetsheet.Cells(rng.Row, 91).Value
        ActiveSheet.Range("D9").Value = targetsheet.Cells(rng.Row, 89).Value

Another thing I see is that you haven't defined what rng is supposed to represent.
 
Upvote 0
johnnyL said:
VBA Code: 
        ActiveSheet.Range("B9").Value = targetsheet.Cells(rng.Row, 90).Value
        ActiveSheet.Range("C9").Value = targetsheet.Cells(rng.Row, 91).Value
        ActiveSheet.Range("D9").Value = targetsheet.Cells(rng.Row, 89).Value

Another thing I see is that you haven't defined what rng is supposed to represent.
Click to expand...
Goodmorning. I see now. Im not sure how to tie it to the text in the specific cell in the sheet yet though.
 
Upvote 0
VBA Code: 
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 target As Workbook
Set target = Workbooks("New RI Form rev9 - Develop Mode - DO NOT USE.xlsb")

Dim targetsheet As Worksheet
Set targetsheet = target.Sheets("RI Log")

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

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

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

        End If
    Next j

End If
Next

End Sub

I removed the rng. from the string and still does not pull the value from the targetsheet
 
Upvote 0
Why don't you explain what you want that last loop to do. I am asking because I guarantee the code you have in that loop is not going to do it.
 
Upvote 0
johnnyL said:
Why don't you explain what you want that last loop to do. I am asking because I guarantee the code you have in that loop is not going to do it.
Click to expand...
I agree.

The code should act as follows.

1. Verify textbox 1-20 have data
2. If there is information then create a new sheet at the end of the sheets in the active workbook.
3. Search for textbox.value in second sheet (New RI Form rev9.....)
4. If the textbox.value is found in second sheet, then add the following to activesheet
a. A to cell A9 (of activesheet)
b. (New RI Form rev9) column 90 value of current row to cell B9 (of activesheet)
c. (New RI Form rev9) column 91 value of current row to cell C9 (of activesheet)
d. (New RI Form rev9) column 89 value of current row to cell D9 (of activesheet)
5. Repeat above until all findings have be added to activesheet

Hope this helps.
 
Upvote 0
DanSMT said:
4. If the textbox.value is found in second sheet, then add the following to activesheet
a. A to cell A9 (of activesheet)
b. (New RI Form rev9) column 90 value of current row to cell B9 (of activesheet)
c. (New RI Form rev9) column 91 value of current row to cell C9 (of activesheet)
d. (New RI Form rev9) column 89 value of current row to cell D9 (of activesheet)
5. Repeat above until all findings have be added to activesheet
Click to expand...

So your intention is to keep overwriting A9,B9,C9&D9 over and over?
 
Upvote 0
johnnyL said:
So your intention is to keep overwriting A9,B9,C9&D9 over and over?
Click to expand...
Nope. Thanks.

Added if "" statement prior as follows

VBA Code: 
Private Sub testcb_Click()

Workbooks("PPAP Template.xlsm").Activate

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 target As Workbook
Set target = Workbooks("New RI Form rev9 - Develop Mode - DO NOT USE.xlsb")

Dim targetsheet As Worksheet
Set targetsheet = target.Sheets("RI Log")

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

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

            If ActiveSheet.Range("A9").Value = "" Then
                ActiveSheet.Range("A9").Value = "A"
            End If
            If ActiveSheet.Range("B9").Value = "" Then
                ActiveSheet.Range("B9").Value = targetsheet.Rows(Row, 90).Value
                'Rows(90).Value
            End If
            'Cells(Row, 90).Value
            If ActiveSheet.Range("C9").Value = "" Then
                ActiveSheet.Range("C9").Value = targetsheet.Range(Rows, 91).Value
            End If
            If ActiveSheet.Range("D9").Value = "" Then
                ActiveSheet.Range("D9").Value = targetsheet.Range(Rows, 89).Value
            End If

        End If
    Next j

End If
Next

End Sub

Im thinking this is the problem.

targetsheet.Range(Rows, 89).Value
 
Upvote 0
Let me try to kick you back toward the right direction:

VBA Code: 
            For j = 2 To targetlastrow
                If targetsheet.Range("B" & j).Value = ActiveSheet.Range("c4").Value Then
                    If ActiveSheet.Range("A9").Value = "" Then ActiveSheet.Range("A9").Value = "A"
                    If ActiveSheet.Range("B9").Value = "" Then ActiveSheet.Range("B9").Value = targetsheet.Cells(j, 90).Value
                    If ActiveSheet.Range("C9").Value = "" Then ActiveSheet.Range("C9").Value = targetsheet.Cells(j, 91).Value
                    If ActiveSheet.Range("D9").Value = "" Then ActiveSheet.Range("D9").Value = targetsheet.Cells(j, 89).Value
                End If
            Next j

If you only have one row that is going to match the C4 value, then you don't need the check for blank in A9 through D9. ;)
 
Upvote 0
johnnyL said:
Let me try to kick you back toward the right direction:

VBA Code: 
            For j = 2 To targetlastrow
                If targetsheet.Range("B" & j).Value = ActiveSheet.Range("c4").Value Then
                    If ActiveSheet.Range("A9").Value = "" Then ActiveSheet.Range("A9").Value = "A"
                    If ActiveSheet.Range("B9").Value = "" Then ActiveSheet.Range("B9").Value = targetsheet.Cells(j, 90).Value
                    If ActiveSheet.Range("C9").Value = "" Then ActiveSheet.Range("C9").Value = targetsheet.Cells(j, 91).Value
                    If ActiveSheet.Range("D9").Value = "" Then ActiveSheet.Range("D9").Value = targetsheet.Cells(j, 89).Value
                End If
            Next j
Click to expand...
I see what you mean. However J is not valid in active sheet ill have to make another last row reference somehow because I also have to pull more information yet that will need another if statement based on null values in column of the same row.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
What's the best way to pull data from this sheet?
Replies
1
Views
114
bobaol
B
A
  • Question
input data from another workbook
Replies
1
Views
424
atditiljazi
A
A
  • Question
Importing cell values from multiple workbooks in one folder
Replies
2
Views
787
Albertu95
A
D
  • Solved
Pulling data from sheet to table on another sheet with excel VBA
Replies
1
Views
791
DanSMT
D
T
  • Question
Insert/Delete Columns issue via VBA
Replies
1
Views
679
Tdorman
T

Forum statistics

Threads
1,216,104
Messages
6,128,856
Members
449,472
Latest member
ebc9

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top