Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
reference cells when looping
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
reference cells when looping
tcell.offset(0,2).value = 2
Isis wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
reference cells when looping
Dave Peterson wrote in
: tcell.offset(0,2).value = 2 Isis wrote: 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 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
reference cells when looping
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. Isis wrote: snipped 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. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
reference cells when looping
Dave Peterson wrote in
: 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. Isis wrote: snipped 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. 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
reference cells when looping
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. Isis wrote: Dave Peterson wrote in : 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. Isis wrote: snipped 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. 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping | Excel Discussion (Misc queries) | |||
Not Looping | Excel Discussion (Misc queries) | |||
Looping thru a range of cells | New Users to Excel | |||
Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
looping through an giving values to cells in vba | Excel Discussion (Misc queries) |