VBA for Excel

[Everx Moorx]

I have a problem.

I'm trying to run a query that will find all of the cells that look like
A@@@-@@@-@@@-@@@-4@@@. An example of the alphanumeric data in my spreadsheet is A001-457-883-540-4556.
All of the cells begin with an 'A'. However, I only want to identify those with a '4' beginning in the last four digits of A001-457-883-540-4556. Here is a sample of my VBA code:

Sub checkValue()
Dim CurCell As Object
Dim Expression As Object
Dim Format As Variant
Format = VBA.Format("A@@@-@@@-@@@-@@@-4@@@")
'Expression = VBA.Format("A@@@-@@@-@@@-@@@-4@@@")
For Each CurCell In Selection
If CurCell.Value = (VBA.Format("A@@@-@@@-@@@-@@@-4@@@")) Then CurCell.Interior.ColorIndex = 6
Next
End Sub

It works if I specifically say A001-457-883-540-4556. But I don't want just one value. I want all values that begin with the number 4 in the last four digits. I hope that someone can help me. Or that I am not asking too much.

[adocwra]

Dim sCell As Object


For Each sCell In Selection
If Left(Right(sCell.Value, 4), 1) = "4" Then
sCell.Interior.ColorIndex = 6
Else
a = sCell.Value
End If
Next
try this

[Everx Moorx]

Thank you so much,

I've been looking for some input on this. I will give your code a try.

Everx

[Everx Moorx]

Hey adocwra,
It worked. Thank you so much.
What am I missing? What did I do wrong in my format?

Everx
;)

[Everx Moorx]

adocwra,

Do you have any idea how I can progrmmatically select those cells that have been turned yellow?

Everx

[adocwra]

YOU COULD KEEP THE ADDRESS IN AN ARRAY CELL.ADDRESS
AND THEN GO BACK AND SELECT THEM
NOT SURE SYNTAX AND I HAVE TO RUN RIGHT NOW TALK LATER IF SOMEONE ELSE HASNT RESPONDED SORRY ABOUT CAPS

[adocwra]

dim i as integer, j as integer,srange as string

i=0
For Each sCell In Selection
If Left(Right(sCell.Value, 4), 1) = "4" Then

sCell.Interior.ColorIndex = 6
arr(i)=scell.address
i=i+1
Else
a = sCell.Value
End If
Next


'Range("D5,D9,F9,I5,I11,J16,K16").Select

'you can build the addresses once you stored them in the array
for j=0 to i
srange=arr(j) & ","
next
srange=mid(srange,1,len(srange)-1)
range( srange ).select

something like that

[Everx Moorx]

You are good! Where did you learn all of this. What resources should I be looking at to strengthen my skills?

[adocwra]

i've been doing vb et al since 91
experience
try try try again
the line i said
was
srange=mid(srange,1,len(srange)-1)
should be
srange=srange & mid(srange,1,len(srange)-1)
the help in excel is good :)
and so is this forum

[Everx Moorx]

adocwra,

I'm sorry to bug you with this. I don't know what you mean by storing the addresses in the array. Do I have to make the selection. I thought that the selection was already done.
I have tried your code and get this message "array(i) sub or function not defined.
:(

[Everx Moorx]

adocwra,

I might as well tell you what I'm trying to accomplish. once I have selected those cells that are yellow ( that have the 4s in the last four digits) I want to group them together and insert a new row beneath them so that I can then insert a new macro into my code. And it will perform another operation on my worksheet.

[adocwra]

sorry I didnt dim the arr()

you have to dim an arr(10) or what ever

I didnt realize you havent done programming else I would have tested it before I gave it to you
I am writing from my head I will degug the code and send it back to you

[adocwra]

what do you mean by this
" want to group them together and insert a new row beneath them .."
can you describe exactyly
do you want to insert a new row when you find them
or do you want to store them on another part of the spread and the do something
:)

[Everx Moorx]

adocwra,

I have done some programming I just didn't understand what you meant there. However, I'm sure not as extensive as you. You are really very good.

What I mean is that I want to sort those numbers(cells) having the 4s in the last 4 digits together and then insert a row beneath that sort. At that point I could use another macro that is already created to do other formatting tasks.

Thanks

[adocwra]

I'm sorry crossed wires here trying not to offend any one :)
and not doing a very good job huh
so you want to get them sorted together in their same row or col
or like i said earlier in another location?

what area are we taling about ?

row
col
array?

[Everx Moorx]

Sort them in the column. But keeping in mind that there are other columns with data in them to that relate to the first so an entire row would look like this:

Column A Column B Column C
A001-457-883-540-4556 Howard Jones 1301 W Hollywood Blv

So all rows that column A's last 4 digits begin with 4 are to be sorted (Brought together), and under them I would like to insert a new row.

Is that sounding a little complicated. I'm sorry speech is not one of my finer points (for that matter neither is programming), But I tried to explain it the best that I could.

Thanx a much

[Everx Moorx]

I mean;

Column A
A001-457-883-540-4556

Column B
Howard Jones

Column C
1301 W Hollywood Blv


Of course the columns are next to each other and not under each other like above.

[adocwra]

so you could add a col before A fill with 0 or 1 and when you find them inset a rating 1 or 0 depending on whether you want them at bottom or top then sort range

[Everx Moorx]

Huh?

I'm so stuck. I can't figure this thing out. Anything that you can do would be greatly appreciated. I'm very grateful for all of your help thus far.

Thank you

We've already figured out how to select them. I know that bubble sorts can be pretty tricky. So I don't want to go that route.
How about this...Suppose I just recorded a macro that did a regular sort (Using the sort buttons on the excel toolbar) and then went into the VBA editor and insert the code that you gave me on selecting those records beginning with 4s in the last four digits. My only problem then will be how to seperate those records from the pack and insert the row beneath them.

[adocwra]

Sub findthefour()



Dim i As Integer, j As Integer, srange As String
Dim sCell As Range

'i = 0
For Each sCell In Selection
If Left(Right(sCell.Value, 4), 1) = "4" Then

sCell.Interior.ColorIndex = 6
sCell.Offset(0, -1).Value = 1


'arr(i) = sCell.Address
'i = i + 1
Else
sCell.Offset(0, -1).Value = 0

End If
Next

End Sub

this will do what i said
just insert a col before a and highlight the number col

[Everx Moorx]

Okay,

This is where I started:

Sub checkValue()
Dim CurCell As Object
Dim sCell As Object
Dim Expression As Object
Dim Format As Variant
For Each sCell In Selection
If Left(Right(sCell.Value, 4), 1) = "4" Then
sCell.Interior.ColorIndex = 6
Else
a = sCell.Value
End If
Next

End Sub

Now should I insert your new code into this, or use it instead of the above. And once I create the Column before Column 'A', should the 0s or 1s correspond to the selected yellow cells or...

[Everx Moorx]

I tried your code alone and got run-time error '1004'

Man...so close

[Everx Moorx]

Hey,

It's me again. I commented out this line.
sCell.Offset(0, -1).Value = 0
and it worked.