-
Jun 11th, 2009, 08:38 AM
#1
Thread Starter
Member
[RESOLVED] Skip cells containing text [Excel]
Hi Guys,
My code have to run though a range of cells in Excel and multiply all values by a factor 0.6.
I need to skip all cells withing the range that contain words(text) or formulas.
My current code manages to skip cells formatted as text or that contains formulas, but not cells containing words and formatted as general.
HTML Code:
mTextFormat = "@"
For mRunner = 1 To mCell.Column - 1
Set aCell = Cells(mCell.Row, mRunner)
On Error GoTo SubErrHandler
If aCell.Value = 0 Then
ElseIf aCell.NumberFormat = mTextFormat Then
ElseIf Left$(aCell.Formula, 1) = "=" Then '-- cell does not contain a formula
Else
aCell.Formula = "=" & aCell.Formula & "*" & mCell
End If
SubErrHandler:
Next
This leaves me with some cells as "#NAME?"
Please assist.
-
Jun 11th, 2009, 09:04 AM
#2
Addicted Member
Re: Skip cells containing text [Excel]
You could always use the below code to just run it if it is a number.
Code:
If Application.IsNumber(aCell.Value) = True Then
Edit: I think you would put this after checking if it's a formula...
Last edited by Fizziii; Jun 11th, 2009 at 09:10 AM.
-
Jun 11th, 2009, 09:28 AM
#3
Thread Starter
Member
Re: Skip cells containing text [Excel]
I knew it had to be simple! Thanks a lot.
If I am allowed a follow up question:
Can one split the for loop to skip a certain value, for example:
For x = 1 to 5 and 7 to 10
At the moment I am repeating the code to achieve the same:
For x = 1 to 5
....
....
For x = 7 to 10
But would be more elegant if I could run all in one loop?
Regards
-
Jun 11th, 2009, 10:02 AM
#4
Addicted Member
Re: Skip cells containing text [Excel]
This is the code I was testing with. I'm pretty sure you can replace "range(myrange)" with "aCell"
Code:
If range(myrange).Value > 1 And range(myrange).Value < 6 Or _
range(myrange).Value > 6 And range(myrange).Value < 10 Then
-
Jun 11th, 2009, 10:08 AM
#5
Thread Starter
Member
Re: Skip cells containing text [Excel]
Not exactly what I am after, I am specifically wanting to use the "for" command, broken into 2 sections.
Please note that this is unrelated to the first question in my post.
-
Jun 11th, 2009, 10:16 AM
#6
Addicted Member
Re: Skip cells containing text [Excel]
Sorry for the confusion. I know it could work if you put an if statement and put everything in that...
Code:
For mRunner = 1 To mCell.Column - 1
If mRunner = 6 Then
Else
Set aCell = Cells(mCell.Row, mRunner)
On Error GoTo SubErrHandler
If aCell.Value = 0 Then
ElseIf aCell.NumberFormat = mTextFormat Then
ElseIf Left$(aCell.Formula, 1) = "=" Then '-- cell does not contain a formula
Else
aCell.Formula = "=" & aCell.Formula & "*" & mCell
End If
End If
SubErrHandler:
Next
-
Jun 11th, 2009, 10:25 AM
#7
Thread Starter
Member
Re: Skip cells containing text [Excel]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|