Hello,
I thought this would be easy but everything I try gives me a blank sheet or a qualifier error. The code on the attached example gives the correct result when using a column reference to lookup Countif result (Sheet Orders Col14) in the For Loop. I want to change the code to use a variable for the "Countif" so that I can remove the column from the worksheet. I have tried using Application.WoksheetFuntion and R1C1 reference for the variable but am getting completely stuck. Hope that makes sense.
Really appreciate the help this site provides to all of us that are learning.
Cheers
Jan
Code
Option Explicit
Sub NewList() 'Create a list of product info for Import
Dim shO As Worksheet, shL As Worksheet
Dim lastrow As Long, i As Long, j As Long, cnt As Long
Dim rng As Range
cnt = 0
Set shO = Worksheets("Data")
Set shL = Worksheets("Export List")
Application.ScreenUpdating = False
'-----Clear List
With shL.Cells(1, 1)
.CurrentRegion.ClearContents
End With
'-----Replicate Rows based on Counts
On Error Resume Next
For i = 4 To shO.Cells(shO.Rows.Count, 2).End(xlUp).Row
'cnt.FormulaR1C1 = "=COUNTIF(RiC7:RiC12,>0)" 'Cannot get this to work. Will replace col references below with variable
shL.Cells(shL.Rows.Count, 1).End(xlUp).Offset(1).Resize(shO.Cells(i, 14).Value, 4).Value = shO.Cells(i, 2).Resize(, 4).Value ' Get data first 4 columns from Orders
If (IsNumeric(Left(shO.Cells(i, 6).Value, 1))) = True Then
shL.Cells(shL.Rows.Count, 5).End(xlUp).Offset(1).Resize(shO.Cells(i, 14).Value, 1).Value = shO.Cells(7, j).Resize(1, j).Value
Else
shL.Cells(shL.Rows.Count, 5).End(xlUp).Offset(1).Resize(shO.Cells(i, 14).Value, 1).Value = shO.Cells(6, j).Resize(1, j).Value
End If
shL.Cells(shL.Rows.Count, 6).End(xlUp).Offset(1).Resize(shO.Cells(i, 14).Value, 1).Value = shO.Cells(i, 6).Resize(, 1).Value ' Get the RRP
Next i
Application.CutCopyMode = False
End Sub
Display More