reference cells when looping

  • Thread starter Thread starter Isis
  • Start date Start date
I

Isis

Using a loop like this

For Each tcell In Range("A4:A17").Cells
If tcell.Value = "Danny" Then
'Write a value into column 2 columns to the right
End If
Next

I want to write a value into the column 2 columns to the right of the
current column and on the same row but I am not sure how to reference
that cell in my code.

So if I find the value "Danny" (as above) in cell A8 - I want to write
the value "2" into cell A10.

Any help appreciated


Thanks
 
tcell.offset(0,2).value = 2


Using a loop like this

For Each tcell In Range("A4:A17").Cells
If tcell.Value = "Danny" Then
'Write a value into column 2 columns to the right
End If
Next

I want to write a value into the column 2 columns to the right of the
current column and on the same row but I am not sure how to reference
that cell in my code.

So if I find the value "Danny" (as above) in cell A8 - I want to write
the value "2" into cell A10.

Any help appreciated

Thanks
Click to expand...
 
tcell.offset(0,2).value = 2
Click to expand...


Dave,

Thanks for that !

If wanted to get the 'alpha' part of the reference and put it into a
variable, how would I do that ?

Thanks again thats a good problem solved.
 
It depends on what you mean.

If you want to extract the characters A-Z and a-z and eliminate everything else,
you could loop through the text and look for those characters.

If you want to keep the first x number of characters (or the last x number of
characters) or even the characters after some indicator (like everything after
the initial hyphen in: xxx-ab23cd), the code would be different.

And what reference do you mean? I'm guessing tcell, but not sure.
 
It depends on what you mean.

If you want to extract the characters A-Z and a-z and eliminate
everything else, you could loop through the text and look for those
characters.

If you want to keep the first x number of characters (or the last x
number of characters) or even the characters after some indicator
(like everything after the initial hyphen in: xxx-ab23cd), the code
would be different.

And what reference do you mean? I'm guessing tcell, but not sure.
Click to expand...


Dave,

thanks for answering - yes I meant the current cell - the current address
that tcell is pointing to - so if it is currently G17 - just the 'G' bit
- but maybe things like offset etc are a better way to go.

Thanks again
 
You can loop through the characters in a string like:

Dim myStr As String
Dim iCtr As Long

myStr = "some string 1234 here"

For iCtr = 1 To Len(myStr)
If Mid(UCase(myStr), iCtr, 1) Like "[A-Z]" Then
'keep it
Else
'replace with a space
Mid(myStr, iCtr, 1) = " "
End If
Next iCtr

'now all the non A-Z's have been replaced with spaces.
'so remove them
myStr = Replace(myStr, " ", "")

MsgBox myStr 'just to show that it worked.
 
Back
Top