Insert formula into cell

[Guest]

Hi,

I am trying to insert a formula into a cell and am having trouble. I am
substituting the variable 'sheetname' for 'Sheet1'.

Here's my code:

Sub AddToList(SheetName)

Dim LastusedrowinD As Long

'The Formula is =Cell("filename" ,'Sheet1'!$A$1)

With Sheets("Table")
LastusedrowinD = .Cells(.Rows.Count, "D").End(xlUp).Row
.cell("D" & LastusedrowinD).Value = _
"=Cell(" & "filename" & ",'" & SheetName & "'!$A$1)"
End With

End Sub

Any suggestions as to how I would add this formula into the cell?

Thanks.

 

[Dave Peterson]

You can either use .cells() or .range(), but you can't mix and match:

With Sheets("Table")
LastusedrowinD = .Cells(.Rows.Count, "D").End(xlUp).Row
.Range("D" & LastusedrowinD).Value = _
"=Cell(""filename"",'" & SheetName & "'!$A$1)"
End With

or

With Sheets("Table")
LastusedrowinD = .Cells(.Rows.Count, "D").End(xlUp).Row
.cells(lastusedrowinD,"D").Value = _
"=Cell(""filename"",'" & SheetName & "'!$A$1)"
End With

======
And watch your quotes surrounding "Filename". You have to double up on them in
VBA to get single "'s in the worksheet formula.

And one more question...

Did you really want to overwrite that last used cell in column D.

I would have guessed:
.Range("D" & LastusedrowinD + 1).Value = ...
or
.cells(lastusedrowinD + 1, "D").Value = ...

Or change the variable name to NextRowToUseInD and use:
NextrowtouseinD = .Cells(.Rows.Count, "D").End(xlUp).Row + 1

 

[Guest]

Try:

With Sheets("Table")
LastusedrowinD = .Cells(.Rows.Count, "D").End(xlUp).Row
.Range("D" & LastusedrowinD).Formula = _
"=Cell(""filename"",'" & SheetName & "'!$A$1)"
End With

 

[Guest]

Thanks to you both. Just when I think I understand it, I find I don't.

I am getting a 'Select Method of Range Class Failed' error on the
..range.select statement in the While loop and I'm confused as to why. It is
the same format of the .range.value statement above it which works, thanks
to you. Can someone clarify?

With Sheets("Table")
NextrowtouseinD = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
.Range("D" & NextrowtouseinD).Value = _
"=Cell(""filename"",'" & sheetname & "'!$A$1)"
While NextrowtouseinD > NextrowtouseinE
NextrowtouseinE = .Cells(.Rows.Count, "E").End(xlUp).Row + 1
.Range("E" & NextrowtouseinE - 1).Select
Selection.Copy
.Range("E" & NextrowtouseinE).Select
Sheets("Table").Paste
Wend
End With

 

[Dave Peterson]

You can only select a range on a sheet that's active.

But lots of times, you don't need to (or want to select):

With Sheets("Table")
NextrowtouseinD = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
.Range("D" & NextrowtouseinD).Value = _
"=Cell(""filename"",'" & sheetname & "'!$A$1)"
While NextrowtouseinD > NextrowtouseinE
NextrowtouseinE = .Cells(.Rows.Count, "E").End(xlUp).Row + 1
.Range("E" & NextrowtouseinE - 1).Copy _
destination:=.Range("E" & NextrowtouseinE)
Wend
End With

But you could select it if you really, really wanted to:

With Sheets("Table")
.select
NextrowtouseinD = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
.Range("D" & NextrowtouseinD).Value = _
"=Cell(""filename"",'" & sheetname & "'!$A$1)"
While NextrowtouseinD > NextrowtouseinE
NextrowtouseinE = .Cells(.Rows.Count, "E").End(xlUp).Row + 1
.Range("E" & NextrowtouseinE - 1).Copy _
destination:=.Range("E" & NextrowtouseinE)
Wend
End With

 

[Guest]

Thank you!

You can only select a range on a sheet that's active.

But lots of times, you don't need to (or want to select):

With Sheets("Table")
NextrowtouseinD = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
.Range("D" & NextrowtouseinD).Value = _
"=Cell(""filename"",'" & sheetname & "'!$A$1)"
While NextrowtouseinD > NextrowtouseinE
NextrowtouseinE = .Cells(.Rows.Count, "E").End(xlUp).Row + 1
.Range("E" & NextrowtouseinE - 1).Copy _
destination:=.Range("E" & NextrowtouseinE)
Wend
End With

But you could select it if you really, really wanted to:

With Sheets("Table")
.select
NextrowtouseinD = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
.Range("D" & NextrowtouseinD).Value = _
"=Cell(""filename"",'" & sheetname & "'!$A$1)"
While NextrowtouseinD > NextrowtouseinE
NextrowtouseinE = .Cells(.Rows.Count, "E").End(xlUp).Row + 1
.Range("E" & NextrowtouseinE - 1).Copy _
destination:=.Range("E" & NextrowtouseinE)
Wend
End With

 

[Guest]

Thanks, Dave!

Is there a way of checking a string and bouncing it unless in contains only
alphnumeric characters?

 

[Dave Peterson]

A-Z and 0-9?

I'd use a function like:

Option Explicit
Function IsAlphaNumeric(myStr As String) As Boolean

Dim iCtr As Long

IsAlphaNumeric = True
For iCtr = 1 To Len(myStr)
If LCase(Mid(myStr, iCtr, 1)) Like "[a-z]" _
Or IsNumeric(Mid(myStr, iCtr, 1)) Then
'ok for both
Else
IsAlphaNumeric = False
Exit For
End If
Next iCtr

End Function

And you can call it in your code with:

Sub testme01()
Dim myStr as string
myStr = "1234qwer."
MsgBox IsAlphaNumeric(mystr)
End Sub

But if you wait around, I bet someone will come up with a Regular Expression
routine that works!

Thanks, Dave!

Is there a way of checking a string and bouncing it unless in contains only
alphnumeric characters?

 

[Guest]

Thanks

A-Z and 0-9?

I'd use a function like:

Option Explicit
Function IsAlphaNumeric(myStr As String) As Boolean

Dim iCtr As Long

IsAlphaNumeric = True
For iCtr = 1 To Len(myStr)
If LCase(Mid(myStr, iCtr, 1)) Like "[a-z]" _
Or IsNumeric(Mid(myStr, iCtr, 1)) Then
'ok for both
Else
IsAlphaNumeric = False
Exit For
End If
Next iCtr

End Function

And you can call it in your code with:

Sub testme01()
Dim myStr as string
myStr = "1234qwer."
MsgBox IsAlphaNumeric(mystr)
End Sub

But if you wait around, I bet someone will come up with a Regular Expression
routine that works!

Thanks, Dave!

Is there a way of checking a string and bouncing it unless in contains only
alphnumeric characters?