Macro question

  • Thread starter Thread starter orquidea
  • Start date Start date
O

orquidea

Hi:

I have the below loop. I want to choose all the rows that have the word
starting with "NFFU (space) and any number" e.i. NFFU 55425 and type on the
next right cell "53". When I run the below macro the loop works columns but
no "53" is being typed in the right cell. Could anyone help me please. I
believe the problem is in this line: If ActiveCell.Value = "NFFU *" Then.

Thanks,
Orquidea




Range("e1").Select

Do
If ActiveCell.Value = "NFFU *" Then
ActiveCell.Offset(0, 1) = "53"
ActiveCell.Offset(1, 0).Select

Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Offset.Value = ""
 
An alternate to using the Left function is this...

If ActiveCell.Value Like "NFFU *" Then

or, if you want to make sure the characters after the "NFFU " are, in fact,
digits only, this...

If ActiveCell.Value Like "NFFU " & String(Len(ActiveCell)-5, "#") Then

Rick
 
Thanks Bernie for your reply. Apparently there is other error in my posted
macro because I can't get the number 53 typed beside "NFFU*". Could you
help me with it please.

Thanks,
 
Thanks Gary for your reply. Apparently there is other error in my posted
macro because I can't get the number 53 typed beside "NFFU*". Could you
help me with it please.

Thanks,
 
Thanks Rick for your reply. Apparently there is other error in my posted
macro because I can't get the number 53 typed beside "NFFU*". Could you
help me with it please.

Thanks,
 
Open an otherwise blank workbook, and run the first macro below to populate
a range with strings. Then run the second to find a value with NFFU<space>,
and put the value 53 next to it.

HTH,
Bernie
MS Excel MVP

Sub MakeNFFUList()
Dim i As Integer
'Populate a range with values
Range("A1").Value = "NFF Strings"
For i = 2 To 20
Cells(i, 1).Value = "NFF" & Chr(70 + i) & " " & 100 + i
Next i
End Sub

Sub FindNFFU()
'Now, look for "NFFU "
Dim myC As Range
For Each myC In Range("A2:A20")
If Left(myC.Value, 5) = "NFFU " Then
myC.Offset(0, 1).Value = 53
End If
Next myC
End Sub
 
If you don't show us the code you are using, we can't see what might be
wrong with it. With that said, here is your original code, simplified
slightly, which implements what I posted...

Sub FindNFFU()
Range("E1").Select
Do
If ActiveCell.Value Like "NFFU *" Then
ActiveCell.Offset(0, 1) = "53"
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset.Value = ""
End Sub

However, look at Bernie's last posting to you for the more preferred method
of not selecting each cell individually in order to do "stuff" with it.

Rick
 
thanks,

Rick Rothstein (MVP - VB) said:
If you don't show us the code you are using, we can't see what might be
wrong with it. With that said, here is your original code, simplified
slightly, which implements what I posted...

Sub FindNFFU()
Range("E1").Select
Do
If ActiveCell.Value Like "NFFU *" Then
ActiveCell.Offset(0, 1) = "53"
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Offset.Value = ""
End Sub

However, look at Bernie's last posting to you for the more preferred method
of not selecting each cell individually in order to do "stuff" with it.

Rick
Click to expand...
 
Back
Top