roundaboutrc
New Member
- Joined
- Jul 24, 2019
- Messages
- 5
I am running into an issue where I can hard code in a WorkBook and WorkSheet Names into a macro and it will run properly. But when I dim a variable(Workbook,Worksheet) and SET the objects I am unable or more less don't know how to reference those objects in a countIfS formula. Which Im not entirely sure I can even do this, but I am hopeful someone might have a solution/answer.
The main reason for not wanting to hard code the Workbook and WorkSheet names in the formula is that I get a new workbook every day that I have to pull counts from and these workbooks are named with date and time of creation as part of the file name. So the first portion of the code looks for the file and sets a variable then the object. All of this works as it should. Until it gets to the formula.
In the countIFs " & wkShtRep & " is what I can delete and hardcode in the WorkBook/Worksheet. So how can I reference between the workbooks to get this to work.
The main reason for not wanting to hard code the Workbook and WorkSheet names in the formula is that I get a new workbook every day that I have to pull counts from and these workbooks are named with date and time of creation as part of the file name. So the first portion of the code looks for the file and sets a variable then the object. All of this works as it should. Until it gets to the formula.
In the countIFs " & wkShtRep & " is what I can delete and hardcode in the WorkBook/Worksheet. So how can I reference between the workbooks to get this to work.
Code:
Dim fDate As String 'todays date
Dim fName As String 'file name with wild card
Dim fDir As String 'directory of file
Dim fFull_Name As String 'found file, full file name
Dim sPath As String 'full file path
Dim wbReplenishment As Workbook 'Replenishment planner as workbook
Dim wkShtRep As Worksheet 'DataSet sheet as worksheet
Dim lr As Long 'Last Row
Dim wbRpt As Workbook 'Report
Dim wkShtRpt1 As Worksheet 'Device Specific as a worksheet
Dim wkShtRpt2 As Worksheet 'Universal-Short as a worksheet
'Find the Data Source
fDate = Format(Date, "yyyy-mm-dd")
fName = "Replenishment Planner_" & fDate & "_*"
fDir = ("/Users/rc/Downloads/")
fFull_Name = ""
On Error Resume Next
fFull_Name = Dir(fDir & fName)
On Error GoTo 0
If fFull_Name = "" Then
MsgBox "File doesn't exist"
Else
sPath = fDir & fFull_Name
Set wbReplenishment = Workbooks.Open(sPath, True, True)
End If
Set wbRpt = ThisWorkbook
Set wkShtRpt1 = ThisWorkbook.Worksheets("Device Specific")
Set wkShtRpt2 = ThisWorkbook.Worksheets("Universal-Short")
Set wkShtRep = wbReplenishment.Worksheets("DataSheet")
wbRpt.Activate
With wkShtRpt1
lr = Range("B" & .Rows.Count).End(xlUp).Row
.Range("F2").Formula = _
"=COUNTIFS(" & wkShtRep & "!$F:$F,$A2," & wkShtRep & "!$C:$C,""OnHand""," & wkShtRep & "!$L:$L,INDEX('Easy Product IDs'!A:I,MATCH($B2,'Easy Product IDs'!$A:$A,0),2))"
.Range("F2").AutoFill .Range("F2:F" & lr)
.Range("F2:F" & lr).Copy
.Range("F2:F" & lr).PasteSpecial Paste:=xlPasteValues
End With
