Macro Help

~Zodiac~

Board Regular
Joined
Jan 10, 2005
Messages
70
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!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Haluk

Rules Violation
Joined
Oct 26, 2002
Messages
1,075
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;

Code:
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~

Board Regular
Joined
Jan 10, 2005
Messages
70
thanks for your help!

sry for my late reply, i rebuild the macro in the maintime :oops: 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
:(
 

Forum statistics

Threads
1,147,847
Messages
5,743,522
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top