Way to modify existing VBA to pass through blank values on import from one Worksheet to Another?

Question

I've been searching for a method to convert the "Excellent Answer" (VBA Code) in the below answer from "JDH". (I did not feel it was appropriate to try and contact directly for further help with his answer below)

The VBA answer in the below response/answer is 90% perfect for what I need except that once I get to reading the data for my Part# and Order Qty in the Source Workbook (Source could be up to 5000 lines of products and has been filtered down to hide rows with blank Order Qty's), the below VBA will copy ALL data within the range whether it's been filtered or not.

(Below is 90+% close to what I need) https://stackoverflow.com/a/7878070/1413702

I've modified the code to work for my instance and everything works great until I get to having to read through the data for Part # & Order Qty's that are not blank. I only want to bring over the Part # and Order Qty if the Order Qty is not blank and realize I need to possibly read through the entire range of 5000 lines to insure getting all potentially ordered items. If it was a straight source range to target range, the above would be perfect however, since there's the possibility the Source has been filtered, there's hidden rows within the range that need to be checked for Order Qty Blank. Also, there's an overall LIMIT to the number of import occurrences that can occur at this point because the order form is only set up for 501 max lines. 300 is a general rule with 501 being the safeguard. My revision is below however, I have not accounted for reading through potentially 5000 potential lines as that was an afterthought and I'm getting an error right out of the shoot that's noted when I tried to check for a blank value. Please help if you can and, again, please advise if I've posted incorrectly. I will change whatever is necessary to conform to forum rules. Thank You, khleisure

My Revised Code of "JDH's" excellent answer as follows:

Private Sub ImportExternalDataToOrderForm_Click()
  '*******Exit Sub - Used to disable command button till sub written/executes properly
  ' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook


     ' Active workbook is the Target Workbook
Set targetWorkbook = Application.ActiveWorkbook

     ' get the customer workbook to use as Source WorkBook
filter = "XLS files (*.xls),*.xls"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)

Set customerWorkbook = Application.Workbooks.Open(customerFilename)

     ' Ranges vary in Source Workbook to Target Workbook but, applicable data to import      
     ' to Order Form

     'Import data from customer(source) to target workbook(active Order Form)
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(2)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)

targetSheet.Range("B4").Value = sourceSheet.Range("C2").Value ' Works Great
targetSheet.Range("B9").Value = sourceSheet.Range("C8").Value ' Works Great
targetSheet.Range("G9").Value = sourceSheet.Range("C9").Value 'Works Great
targetSheet.Range("N4:N6").Value = sourceSheet.Range("N2:N4").Value ' Works Great
targetSheet.Range("J18:J20").Value = sourceSheet.Range("K7:K9").Value ' Works Great

 ' Below 2 lines work great however, the Source Workbook is filtered to eliminate
 ' blanks in Order Qty Field (Starting Source M13) and the 2 lines of code below bring     
 ' over everything in the overall range of 501 possible occurrences regardless if it's 
 ' filtered or not.  Blank Order Qty fields that have been filtered should not be    
 ' imported.  Max lines to import is defined by range of 501 max

     'below xfers the Part Number from A column range of Source to A column Range of 
     'Target and works great except no function to check for blanks in Order Qty
     ' Below works exactly how it's written to work 

'targetSheet.Range("A27:A527").Value = sourceSheet.Range("A13:A513").Value

     'below xfers the Ordered Qty from M column range of Source to D column Range of 
     'Target and this is where I need to check if a qty has been ordered (or not =       
     'blank) in order to perform the above import and this import.  The 2 are 
     'relational to one another
     ' Below works exactly how it's written to work but, needs to 1st check for blank

'targetSheet.Range("D27:D527").Value = sourceSheet.Range("M13:M513").Value

     '*****My attempt to modify further to account for blank value
     'Need loop to read through each row and import Source Range "A" to Target Range 
     '"A" along with associated Source Range "M" to Target Range "D".  Max 501 lines
     '*****

     ' Need to use loop for Part Number and associated Order Qty
Dim t As Long
Dim s As Long
Dim i As Long
      '*****

t = 27     ' row number on target where Product # (Col A) and Order Qty (Col D) start
s = 13     ' row number on Source where Product # (Col A) and Order Qty (Col M) start
i = 1      ' set counter for total of 501 potential import occurrences Max
           ' Need to establish reading potential Source rows (filtered or not) at 5000 
           ' max rows (most likely range of 3500)
           ' for most factories and their offerings.  (Have not established this 
           ' portion yet)

For i = 1 To 501 Step 1
   If **sourceSheet.Range("M(s)").Value** = "" Then ' Error Here ****************
                                **'Method 'Range' of object '_Worksheet' failed**
     Next i
     Exit Sub
Else
     targetSheet.Range("A(t)").Value = sourceSheet.Range("A(s)").Value ' xfer Part #
     targetSheet.Range("D(t)").Value = sourceSheet.Range("M(s)").Value ' xfer Order Qty
End If
    t = t + 1
    s = s + 1
Next i

    ' Close Customer(Source) workbook[/COLOR]
customerWorkbook.Close

End Sub

score 0 · Answer 1 · answered May 24 '12 at 23:45

Believe I solved this for what I wanted it to do. Still testing but, thus far, the below is reading through the source while determining if the Source "Order Qty" is blank and moving on till the Source "Order Qty" has an amt entered and importing the corresponding Part # and Ordered Qty respectively for one another. It also passes through or considers blank Order Qty amounts or rows that may have been filtered out on the Source due to the Order Qty Field having a blank. As per below and if anyone can help answer the error I left in the comments of the code while trying to use a varied range for the Source, it would be appreciated. Tia, khleisure

Private Sub ImportExternalDataToOrderForm_Click()
'*******Exit Sub - Used to disable command button till sub written
' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook


' Active workbook is the Target Workbook
Set targetWorkbook = Application.ActiveWorkbook

' get the customer workbook to use as Source WorkBook
filter = "XLS files (*.xls),*.xls"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)

Set customerWorkbook = Application.Workbooks.Open(customerFilename)

' Ranges vary in Source Workbook to Target Workbook but, applicable data to import to Order Form
' Import data from customer(source) to target workbook(active Order Form)
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(2)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)

targetSheet.Range("B4").Value = sourceSheet.Range("C2").Value ' Works Great
targetSheet.Range("B9").Value = sourceSheet.Range("C8").Value ' Works Great
targetSheet.Range("G9").Value = sourceSheet.Range("C9").Value 'Works Great
targetSheet.Range("N4:N6").Value = sourceSheet.Range("N2:N4").Value ' Works Great
targetSheet.Range("J18:J20").Value = sourceSheet.Range("K7:K9").Value ' Works Great

     ' below 2 lines work for fixed range and every line regardless if filtered
     ' and regardless if Order Qty is blank
'targetSheet.Range("A27:A527").Value = sourceSheet.Range("A13:A513").Value
'targetSheet.Range("D27:D527").Value = sourceSheet.Range("M13:M513").Value

'***** LOOP THOUGH PRODUCT AND QTY ORDERED DATA FOR BALANCE OF IMPORT
' Need to Loop through all Rows of overall Source (Starting R#13) to account
' for filtered lines that exist between the lines that remain and have a qty
' in the Order Qty Field (Col M).  If Qty Ordered Blank (filtered) you pass up
' the import of Source A & M to Target A & D and move to next.  If Qty Ordered from
' Source has a Qty entered, you drop through to import accordingly from Source to
' to Target.  Set Currently at Max Source of Range A13:A3000 (Can increase if
' necessary.  Also, counter to limit the number of imports to max 501 per Order
' Form's limit of lines currently.  Have to modify Order Form and loop below if more

Dim t As Long
Dim s As Long
Dim r As Long
'Dim rcount As Long (removed due to error below)
'*****

t = 27 ' Target Starting Row to accept imported data
s = 13 ' Source Starting Row to begin import consideration
r = 13 ' Define Start counter in For/Next below
       ' with Max set to 3000 potential rows currently (can increase if necessary)

'rcount = Workbook(sourceSheet).Cells(RowCount, "a").End(xlUp).Row ' error here
'rcount = customerWorkbook.Worksheets(1).Cells(RowCount, "a").End(xlUp).Row
      'Above Line creates Error 1004 Application-defined or Object-defined Error

' For r = r to rcount Step 1 (removed because of above error)

For r = r To 3000 Step 1

    If t <= 527 Then ' 501 max occurrences that can import data "t" starts at 27

       If sourceSheet.Range("M" & s).Value = "" Then

            If r = 3000 Then
              customerWorkbook.Close
              Exit Sub
            End If

         s = s + 1

       Else

         targetSheet.Range("A" & t).Value = sourceSheet.Range("A" & s).Value
         targetSheet.Range("D" & t).Value = sourceSheet.Range("M" & s).Value
         t = t + 1
         s = s + 1

       End If

    Else
       customerWorkbook.Close
       Exit Sub

    End If

Next r


' Close customer workbook
customerWorkbook.Close

End Sub

Way to modify existing VBA to pass through blank values on import from one Worksheet to Another?

1 Answers1