Error: variable in vba formula

[kdreed15]

I must have messed up the vba syntax here but I can't figure it out. The error is in the last line. Any help would be appreciated!

RangeName = InputBox("Enter the name you used to define the data from the last sheet imported. (Must be exact!)")

ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=countifs('[" & RangeName & "]'!,WTX)"

 

[Phanmore]

ActiveCell.Formula = "=countifs('[" & RangeName & "]'!,""WTX"")" <!-- / message -->

 

[kdreed15]

It is still giving me an obect or application run time error

 

[kdreed15]

Here is the whole code.



Sub Find_Date()
RangeName = Range("D3").Value

Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter Date of last entry exactly as it appears in cell")
If Trim(FindString) <> "" Then
With Sheets("Historical Data").Range("4:4")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
End If
'This will insert the necessary formulas

ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]+7"


ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=countifs('[" & RangeName & "]'!,""WTX"")"

End Sub

 

[Jonmo1]

What does the formula look like if you manually enter it in the sheet?

Named ranges don't require the '[]'! around them...
so it probably looks like
=COUNTIFS(RangeName,"WTX") right?

try
ActiveCell.Formula = "=countifs(" & RangeName & ",""WTX"")"

 

[kdreed15]

Yeah I thought of that too and tried it but it is still not working.

 

[Jonmo1]

OK, so..

1. What is the exact formula you expect to be entered in the cell ?
2. What is the value of the variable RangeName ?

 

[pbornemeier]

ActiveCell.Formula = "=COUNTIFS(" & RangeName & ",""WTX"")"
worked for me.

 

[kdreed15]

OK, so..

1. What is the exact formula you expect to be entered in the cell ?
2. What is the value of the variable RangeName ?

1. The formula I want is: =COUNTIFS(Data.10.14.11,"WTX")
This formula works if manually entered in.

2. The value that I typed into cell "D2" is Data.10.14.11

Basically what I did was defined a name for a range of cells on a different sheet. I want to be able to make that name a variable because it will change weekly. Maybe there is a completely different way of doing this. I tried an input box but was getting the same error.

 

[Jonmo1]

Ok, so if you put Data.10.14.11 in D2...

But your code is written

RangeName = Range("D3").Value

Then RangeName is getting the wrong value..