ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Insert formula into cell (https://www.excelbanter.com/excel-programming/396805-insert-formula-into-cell.html)

Karen53
Insert formula into cell
 
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
Insert formula into cell
 
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




Karen53 wrote:

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

JMB
Insert formula into cell
 
Try:

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



"Karen53" wrote:

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.

Karen53
Insert formula into cell
 
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" wrote:

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




Karen53 wrote:

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

Dave Peterson
Insert formula into cell
 
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

Karen53 wrote:

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" wrote:

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




Karen53 wrote:

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


--

Dave Peterson

Karen53
Insert formula into cell
 
Thank you!

"Dave Peterson" wrote:

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

Karen53 wrote:

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" wrote:

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




Karen53 wrote:

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


--

Dave Peterson

Karen53
Insert formula into cell
 
Thanks, Dave!

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



"Dave Peterson" wrote:

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

Karen53 wrote:

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" wrote:

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




Karen53 wrote:

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


--

Dave Peterson

Dave Peterson
Insert formula into cell
 
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!

Karen53 wrote:

Thanks, Dave!

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

"Dave Peterson" wrote:

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

Karen53 wrote:

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" wrote:

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




Karen53 wrote:

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


--

Dave Peterson


--

Dave Peterson

Karen53
Insert formula into cell
 
Thanks

"Dave Peterson" wrote:

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!

Karen53 wrote:

Thanks, Dave!

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

"Dave Peterson" wrote:

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

Karen53 wrote:

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" wrote:

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




Karen53 wrote:

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


--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 03:42 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com