Macro Help

[~Zodiac~]

how can i search for the first cell with the "AA1111" format (2 Alphas and 4 numbers)?

the search must be excecuted in whole cells only, firts on the "Moves" sheet


Sheets("Moves").Select
Range(StartValue).Select ' i defined the Startvalue as an adress in a previous piece of the macro

'what to put here? (this is where i want to search for a cell with the chosen format)
Temp1=ActiveCell.Value
Rij=ActiveCell.Row
Kolom=ActiveCell.Column
Cells(Rij, Kolom+1).Select
ValueA=ActiveCell.Value

Sheets("Scrap").Select
Cells.Find(What:=Temp1, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate

Rij=ActiveCell.Row
Kolom=ActiveCell.Column
Cells(Rij, Kolom+1).Select
ValueB=ActiveCell.Value

Sheets("PPM").Select
Cells.Find(What:=Temp1, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate

Rij=ActiveCell.Row
Kolom=ActiveCell.Column
Cells(Rij, Kolom+1).Select
Cells.Formula = "= 1000000 /" +ValueA+"*"+ValueB ' if the "1000000/" ValueA results in the difision by zero error i want the formula to be "0" zero




any suggestions about how to shorten the code are welcome to

i hope someone will help me out

Thanks in Advance!

 

[Haluk]

how can i search for the first cell with the "AA1111" format (2 Alphas and 4 numbers)?

Try this and ammend it to suit your needs;

Sub Test()
    Dim MyRng As Range
        For Each MyRng In Range("A1:A100")
            If LCase(MyRng) Like LCase("[A-Z][A-Z]####") Then
                MsgBox MyRng.Address
            End If
        Next
End Sub

 

[~Zodiac~]

thanks for your help!

sry for my late reply, i rebuild the macro in the maintime making it a bit longer then it was bevore, now it doesn't need to search the format anymore (i made 10 control cells, those look wheter there is a 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9 in the text of the cell in question and what position that number has, if the position is higher then 6 it becomes 0, and if the total of those cells is higher then 2 it becomes the selected cell)


if i had looked sooner it wouldn't be that longly coded and more effective