Need a String Function

[Saladsamurai]

Okay then! :)

Here is a screenshot of my worksheet

http://i12.photobucket.com/albums/a220/saladsamurai/Yest.jpg

I am looping through all of the cells and I need to test the first column to see if it is of the form

R/Letter - Number

or

C/Letter - Number

The only data I want to even look at are those cells with an "R" as the letter.

How can I test this? Basically I want to do something like



If {First letter is an "R"} Then
Do some stuff
End If


I can think of one way to do this, but I know there is a better way.

Thanks!!

[Saladsamurai]

Hmmm, I think InStr() should do it eh?

[CreganTur]

You could use the Left() function, with a 1 for the character parameter- that will pull the very first letter of whatever string value is passed through the function.

HTH:thumb

[p45cal]

the likes of:For Each cll In Range(Range("A2"), Range("A2").End(xlDown))
If Left(cll.Value, 2) = "R/" Then
'do some stuff such as:
cll.Select
MsgBox cll.Address
End If
Next cll
?

[Saladsamurai]

Thanks! p45cal: Could you just explain this line for me

For Each cll In Range(Range("A2"), Range("A2").End(xlDown))

I never use range objects, so I don't unserstand the syntax that well.

It says : For each cell in Range ( Range Definition goes here)

but what is the part in bold?


ALSO: Why doesn't this work?

Sub Test()

Dim i As Long
Dim j As Long
Dim k As Integer
Dim nRow As Long
Dim nCol As Long
Dim nSheet As Integer
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim Summary As Worksheet
Dim IsRack As Boolean
Dim ThisCell As String

nSheet = 2
nRow = 250
nCol = 25


Set Summary = Worksheets("Summary")



For k = 1 To 1
For i = 2 To nRow

ThisCell = Worksheets(k).Cells(i, 1)
IsRack = InStr("ThisCell", "R/")
If IsRack = True Then
Summary.Cells(i, 1) = Worksheets(k).Cells(i, 1)
Summary.Cells(i, 2) = Worksheets(k).Cells(i, 10)
End If

Next i
Next k



End Sub


The variable "IsRack" is always False for some reason...

[Saladsamurai]

I don't understand why this isn't working? It is not writing anything to sheet "Summary"

Sub Test()

Dim i As Long
Dim j As Long
Dim k As Integer
Dim nRow As Long
Dim nCol As Long
Dim nSheet As Integer
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim Summary As Worksheet
Dim ThisCell As String
Dim Dummy As String
nSheet = 2
nRow = 250
nCol = 25


Set Summary = Worksheets("Summary")



For k = 1 To 1
For i = 2 To nRow

ThisCell = LTrim(Worksheets(k).Cells(i, 1))
Dummy = Left(ThisCell, 2)
If Left(ThisCell, 2) = "/R" Then
Summary.Cells(i, 1) = Worksheets(k).Cells(i, 1)
Summary.Cells(i, 2) = Worksheets(k).Cells(i, 10)
End If

Next i
Next k



End Sub


I inserted a Break point at "Next i" and the value of "Dummy" is indeed "/R"

So why isn't it writing anything?

edit: attached file

Could the "/" symbol be messing things up somehow?

[p45cal]

Range("A2")
is the single cell A2.
Range("A2:C3")
is the block of cells A2:C3.
Another form of this is
Range(cell1, cell2)
so the same range as above could be written:
Range(Range("A2"),Range("C3"))
Because I didn't know how far the column went down, I made the assumption that there were no gaps in that first column and used:
Range("A2").end(xldown)
as the second parameter.
.End(xldown)
is the same as pressing End then the Down Arrow on the keyboard in this case starting at A2). So wherever that ends up is the second parameter (cell2):
Range(Range("A2"), Range("A2").End(xlDown))

As to why it's not writing anything to the summary sheet, at least one reason is that it's not finding /R because your cells are more likely to contain R/. So change:
If Left(ThisCell, 2) = "/R" Then
change it to:If Left(ThisCell, 2) = "R/" Then

[Saladsamurai]

Wow. It's time for a prescription change or something. Thanks!


Range("A2")
is the single cell A2.
Range("A2:C3")
is the block of cells A2:C3.
Another form of this is
Range(cell1, cell2)
so the same range as above could be written:
Range(Range("A2"),Range("C3"))
Because I didn't know how far the column went down, I made the assumption that there were no gaps in that first column and used:
Range("A2").end(xldown)
as the second parameter.
.End(xldown)
is the same as pressing End then the Down Arrow on the keyboard in this case starting at A2). So wherever that ends up is the second parameter (cell2):
Range(Range("A2"), Range("A2").End(xlDown))

[/vba]

Okay thats great! Thanks again :)