About Us

Karen53

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find a cell and then insert a formula into adjacent cell	crowdx42[_8_]	Excel Programming	7	August 21st 06 02:53 PM
Insert a formula into the last row of a cell	RoVo	Excel Programming	2	August 2nd 06 09:19 PM
Insert Formula in Cell with VBA	Ctech[_137_]	Excel Programming	1	April 5th 06 11:27 AM
Using VBA to Insert a formula in a cell	Lee	Excel Programming	2	March 17th 06 01:23 AM
Insert value of formula into cell	Bill	Excel Programming	16	August 1st 05 06:16 PM

Menu

About Us