Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
After checked condition, add the data to adjacent cell automatically
Hi all,
Could anyone help me how to check condition, and fill the data to the adjacent cell automatically? Such that, there are several columns at the worksheet. And I specified to use 2 columns (C and D) only. Column D contains characters in each cell. Column C is blank Column without data in all cell. It checks the number of the characters in each cell of column D. If the number of characters is 10 in D2, then fill "5" into C2 If the number of characters is 20 in D3, then fill "4" into C3 How can I implement that operation? Thanks for your in advance. TLee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
After checked condition, add the data to adjacent cell automatically
You may need to fiddle the numbers but, this should give you the idea:
=IF(LEN(D1)<10,5,IF(LEN(D1)<20,4,IF(LEN(D1)<30,3,I F(LEN(D1)<40,2,IF(LEN(D1)=40,1))))) OK, that's a lot to take on board in one hit so let's go at it step-by-step: The Syntax for If() =IF(condition, If TRUE, If FALSE) Add the condition: =IF(LEN(D1)<10, If TRUE, If FALSE) Now add the If TRUE part =IF(LEN(D1)<10,5, If FALSE) Now add the if FALSE part (which is another If() statement, until the last nested IF when you use a value) =IF(LEN(D1)<10,5, IF(condition, If TRUE, If FALSE)) Keep building replacing your condition, true and false parts until you cover all conditions. There is a limit of seven nested Ifs. -- Steve "tlee" wrote in message ... Hi all, Could anyone help me how to check condition, and fill the data to the adjacent cell automatically? Such that, there are several columns at the worksheet. And I specified to use 2 columns (C and D) only. Column D contains characters in each cell. Column C is blank Column without data in all cell. It checks the number of the characters in each cell of column D. If the number of characters is 10 in D2, then fill "5" into C2 If the number of characters is 20 in D3, then fill "4" into C3 How can I implement that operation? Thanks for your in advance. TLee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
After checked condition, add the data to adjacent cell automatically
Sorry. I just noticed that you want to automate the process with code rather
than use a function. Sub Test() ' Select column D cells ' Results show in Column C For Each c In Selection x = Len(c) Select Case x Case Is < 10 rtn = 5 Case Is < 20 rtn = 4 Case Is < 30 rtn = 3 Case Is < 40 rtn = 2 Case Is < 50 rtn = 1 Case Else rtn = 0 End Select c.Offset(, -1) = rtn Next c End Sub -- Steve "tlee" wrote in message ... Hi all, Could anyone help me how to check condition, and fill the data to the adjacent cell automatically? Such that, there are several columns at the worksheet. And I specified to use 2 columns (C and D) only. Column D contains characters in each cell. Column C is blank Column without data in all cell. It checks the number of the characters in each cell of column D. If the number of characters is 10 in D2, then fill "5" into C2 If the number of characters is 20 in D3, then fill "4" into C3 How can I implement that operation? Thanks for your in advance. TLee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
After checked condition, add the data to adjacent cell automatically
Hi AltaEgo,
Thanks for your help. You give me the idea how to implement the macro. Besides, anyone know how to selected the specified column pattern for checking? Such that, check column (B, D, F, H, J, L,........) in the worksheet. I tried to find information about it. It is more likely to use "range.Offset( )" to implement. Am I correct? how can I loop it until checked all specified columns. Thanks for your in advance. Best regards, Tom Lee Sorry. I just noticed that you want to automate the process with code rather than use a function. Sub Test() ' Select column D cells ' Results show in Column C For Each c In Selection x = Len(c) Select Case x Case Is < 10 rtn = 5 Case Is < 20 rtn = 4 Case Is < 30 rtn = 3 Case Is < 40 rtn = 2 Case Is < 50 rtn = 1 Case Else rtn = 0 End Select c.Offset(, -1) = rtn Next c End Sub -- Steve "tlee" wrote in message ... Hi all, Could anyone help me how to check condition, and fill the data to the adjacent cell automatically? Such that, there are several columns at the worksheet. And I specified to use 2 columns (C and D) only. Column D contains characters in each cell. Column C is blank Column without data in all cell. It checks the number of the characters in each cell of column D. If the number of characters is 10 in D2, then fill "5" into C2 If the number of characters is 20 in D3, then fill "4" into C3 How can I implement that operation? Thanks for your in advance. TLee |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
After checked condition, add the data to adjacent cell automatically
Option Explicit
Sub demo() manager "C" End Sub Sub manager(col As String) Dim source As Range Dim cell As Range Set source = Columns(col).SpecialCells(xlCellTypeConstants) If Not source Is Nothing Then For Each cell In source.Cells Select Case Len(cell.Value) Case 10 cell.Offset(, 1).Value = 5 Case 20 cell.Offset(, 1).Value = 4 Case Else End Select Next End If End Sub "tlee" wrote in message ... Hi all, Could anyone help me how to check condition, and fill the data to the adjacent cell automatically? Such that, there are several columns at the worksheet. And I specified to use 2 columns (C and D) only. Column D contains characters in each cell. Column C is blank Column without data in all cell. It checks the number of the characters in each cell of column D. If the number of characters is 10 in D2, then fill "5" into C2 If the number of characters is 20 in D3, then fill "4" into C3 How can I implement that operation? Thanks for your in advance. TLee |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
After checked condition, add the data to adjacent cell automatically
List your columns in an array. Call the other (slightly modified code) for
each element of the array. Sub Test() Dim i, ArrCols ArrCols = Array("D", "F", "H", "J") 'etc For i = LBound(ArrCols) To UBound(ArrCols) Call LenCheck(ArrCols(i)) Next i End Sub Sub LenCheck(Col) dim c,x Range(Col & "2").Select Range(Selection, Selection.End(xlDown)).Select For Each c In Selection x = Len(c) Select Case x Case Is < 10 rtn = 5 Case Is < 20 rtn = 4 Case Is < 30 rtn = 3 Case Is < 40 rtn = 2 Case Is < 50 rtn = 1 Case Else rtn = 0 End Select c.Offset(, -1) = rtn Next c End Sub Depending how many you need to do, selecting and looping each cell may be slow. -- Steve "tlee" wrote in message ... Hi AltaEgo, Thanks for your help. You give me the idea how to implement the macro. Besides, anyone know how to selected the specified column pattern for checking? Such that, check column (B, D, F, H, J, L,........) in the worksheet. I tried to find information about it. It is more likely to use "range.Offset( )" to implement. Am I correct? how can I loop it until checked all specified columns. Thanks for your in advance. Best regards, Tom Lee Sorry. I just noticed that you want to automate the process with code rather than use a function. Sub Test() ' Select column D cells ' Results show in Column C For Each c In Selection x = Len(c) Select Case x Case Is < 10 rtn = 5 Case Is < 20 rtn = 4 Case Is < 30 rtn = 3 Case Is < 40 rtn = 2 Case Is < 50 rtn = 1 Case Else rtn = 0 End Select c.Offset(, -1) = rtn Next c End Sub -- Steve "tlee" wrote in message ... Hi all, Could anyone help me how to check condition, and fill the data to the adjacent cell automatically? Such that, there are several columns at the worksheet. And I specified to use 2 columns (C and D) only. Column D contains characters in each cell. Column C is blank Column without data in all cell. It checks the number of the characters in each cell of column D. If the number of characters is 10 in D2, then fill "5" into C2 If the number of characters is 20 in D3, then fill "4" into C3 How can I implement that operation? Thanks for your in advance. TLee |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
After checked condition, add the data to adjacent cell automatically
Thank you all
Tlee Option Explicit Sub demo() manager "C" End Sub Sub manager(col As String) Dim source As Range Dim cell As Range Set source = Columns(col).SpecialCells(xlCellTypeConstants) If Not source Is Nothing Then For Each cell In source.Cells Select Case Len(cell.Value) Case 10 cell.Offset(, 1).Value = 5 Case 20 cell.Offset(, 1).Value = 4 Case Else End Select Next End If End Sub "tlee" wrote in message ... Hi all, Could anyone help me how to check condition, and fill the data to the adjacent cell automatically? Such that, there are several columns at the worksheet. And I specified to use 2 columns (C and D) only. Column D contains characters in each cell. Column C is blank Column without data in all cell. It checks the number of the characters in each cell of column D. If the number of characters is 10 in D2, then fill "5" into C2 If the number of characters is 20 in D3, then fill "4" into C3 How can I implement that operation? Thanks for your in advance. TLee |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
After checked condition, add the data to adjacent cell automatically
Hi Steve,
Could you help to explain the following 2 statements? Range(Col & "2").Select Range(Selection, Selection.End(xlDown)).Select Thanks, Tlee List your columns in an array. Call the other (slightly modified code) for each element of the array. Sub Test() Dim i, ArrCols ArrCols = Array("D", "F", "H", "J") 'etc For i = LBound(ArrCols) To UBound(ArrCols) Call LenCheck(ArrCols(i)) Next i End Sub Sub LenCheck(Col) dim c,x Range(Col & "2").Select Range(Selection, Selection.End(xlDown)).Select For Each c In Selection x = Len(c) Select Case x Case Is < 10 rtn = 5 Case Is < 20 rtn = 4 Case Is < 30 rtn = 3 Case Is < 40 rtn = 2 Case Is < 50 rtn = 1 Case Else rtn = 0 End Select c.Offset(, -1) = rtn Next c End Sub Depending how many you need to do, selecting and looping each cell may be slow. -- Steve "tlee" wrote in message ... Hi AltaEgo, Thanks for your help. You give me the idea how to implement the macro. Besides, anyone know how to selected the specified column pattern for checking? Such that, check column (B, D, F, H, J, L,........) in the worksheet. I tried to find information about it. It is more likely to use "range.Offset( )" to implement. Am I correct? how can I loop it until checked all specified columns. Thanks for your in advance. Best regards, Tom Lee Sorry. I just noticed that you want to automate the process with code rather than use a function. Sub Test() ' Select column D cells ' Results show in Column C For Each c In Selection x = Len(c) Select Case x Case Is < 10 rtn = 5 Case Is < 20 rtn = 4 Case Is < 30 rtn = 3 Case Is < 40 rtn = 2 Case Is < 50 rtn = 1 Case Else rtn = 0 End Select c.Offset(, -1) = rtn Next c End Sub -- Steve "tlee" wrote in message ... Hi all, Could anyone help me how to check condition, and fill the data to the adjacent cell automatically? Such that, there are several columns at the worksheet. And I specified to use 2 columns (C and D) only. Column D contains characters in each cell. Column C is blank Column without data in all cell. It checks the number of the characters in each cell of column D. If the number of characters is 10 in D2, then fill "5" into C2 If the number of characters is 20 in D3, then fill "4" into C3 How can I implement that operation? Thanks for your in advance. TLee |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
After checked condition, add the data to adjacent cell automatically
The Test() Sub is an array of the columns you wish to check. It calls them
one-by-one passing the value to LenCheck through the parameter named 'Col'. So, for each of the columns listed in the array, LenCheck runs: Range(Col & "2").Select For example, if D is passed from the array, the above can be read as Range("D2").select Or, the same as click on Cell D2. Range(Selection, Selection.End(xlDown)).Select Selects all cells from the current selection (D2 in the example) to the cell above the next blank cell in the column, moving down. Or, the same as holding the [Shift] key and pressing [End]/[Down arrow] Note Range(Selection, Selection.End(xlDown)).Select assumes there are no blank rows in the area you wish to check. If you are having problems because of blank rows substitute: 'select last cell in row Range(Col & "65536").Select 'move selection up to last cell with a value Selection.End(xlUp).Select. 'select from that point to the row 2 cell Range(Selection, Range(Col & "2")).Select in place of Range(Col & "2").Select Range(Selection, Selection.End(xlDown)).Select The choice you make depends on data and layout. If you have summary below, you need the original code and a blank row between data area and summary. If you have summary in a different area, and may encounter blank rows, use the substitute. -- Steve "tlee" wrote in message ... Hi Steve, Could you help to explain the following 2 statements? Range(Col & "2").Select Range(Selection, Selection.End(xlDown)).Select Thanks, Tlee List your columns in an array. Call the other (slightly modified code) for each element of the array. Sub Test() Dim i, ArrCols ArrCols = Array("D", "F", "H", "J") 'etc For i = LBound(ArrCols) To UBound(ArrCols) Call LenCheck(ArrCols(i)) Next i End Sub Sub LenCheck(Col) dim c,x Range(Col & "2").Select Range(Selection, Selection.End(xlDown)).Select For Each c In Selection x = Len(c) Select Case x Case Is < 10 rtn = 5 Case Is < 20 rtn = 4 Case Is < 30 rtn = 3 Case Is < 40 rtn = 2 Case Is < 50 rtn = 1 Case Else rtn = 0 End Select c.Offset(, -1) = rtn Next c End Sub Depending how many you need to do, selecting and looping each cell may be slow. -- Steve "tlee" wrote in message ... Hi AltaEgo, Thanks for your help. You give me the idea how to implement the macro. Besides, anyone know how to selected the specified column pattern for checking? Such that, check column (B, D, F, H, J, L,........) in the worksheet. I tried to find information about it. It is more likely to use "range.Offset( )" to implement. Am I correct? how can I loop it until checked all specified columns. Thanks for your in advance. Best regards, Tom Lee Sorry. I just noticed that you want to automate the process with code rather than use a function. Sub Test() ' Select column D cells ' Results show in Column C For Each c In Selection x = Len(c) Select Case x Case Is < 10 rtn = 5 Case Is < 20 rtn = 4 Case Is < 30 rtn = 3 Case Is < 40 rtn = 2 Case Is < 50 rtn = 1 Case Else rtn = 0 End Select c.Offset(, -1) = rtn Next c End Sub -- Steve "tlee" wrote in message ... Hi all, Could anyone help me how to check condition, and fill the data to the adjacent cell automatically? Such that, there are several columns at the worksheet. And I specified to use 2 columns (C and D) only. Column D contains characters in each cell. Column C is blank Column without data in all cell. It checks the number of the characters in each cell of column D. If the number of characters is 10 in D2, then fill "5" into C2 If the number of characters is 20 in D3, then fill "4" into C3 How can I implement that operation? Thanks for your in advance. TLee |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
After checked condition, add the data to adjacent cell automatically
Hi Steve,
Thank you very for your detail explanation. It's a great help. I also found the resource for VBA reference from Microsoft http://www.microsoft.com/downloads/d...displaylang=en Tlee The Test() Sub is an array of the columns you wish to check. It calls them one-by-one passing the value to LenCheck through the parameter named 'Col'. So, for each of the columns listed in the array, LenCheck runs: Range(Col & "2").Select For example, if D is passed from the array, the above can be read as Range("D2").select Or, the same as click on Cell D2. Range(Selection, Selection.End(xlDown)).Select Selects all cells from the current selection (D2 in the example) to the cell above the next blank cell in the column, moving down. Or, the same as holding the [Shift] key and pressing [End]/[Down arrow] Note Range(Selection, Selection.End(xlDown)).Select assumes there are no blank rows in the area you wish to check. If you are having problems because of blank rows substitute: 'select last cell in row Range(Col & "65536").Select 'move selection up to last cell with a value Selection.End(xlUp).Select. 'select from that point to the row 2 cell Range(Selection, Range(Col & "2")).Select in place of Range(Col & "2").Select Range(Selection, Selection.End(xlDown)).Select The choice you make depends on data and layout. If you have summary below, you need the original code and a blank row between data area and summary. If you have summary in a different area, and may encounter blank rows, use the substitute. -- Steve "tlee" wrote in message ... Hi Steve, Could you help to explain the following 2 statements? Range(Col & "2").Select Range(Selection, Selection.End(xlDown)).Select Thanks, Tlee List your columns in an array. Call the other (slightly modified code) for each element of the array. Sub Test() Dim i, ArrCols ArrCols = Array("D", "F", "H", "J") 'etc For i = LBound(ArrCols) To UBound(ArrCols) Call LenCheck(ArrCols(i)) Next i End Sub Sub LenCheck(Col) dim c,x Range(Col & "2").Select Range(Selection, Selection.End(xlDown)).Select For Each c In Selection x = Len(c) Select Case x Case Is < 10 rtn = 5 Case Is < 20 rtn = 4 Case Is < 30 rtn = 3 Case Is < 40 rtn = 2 Case Is < 50 rtn = 1 Case Else rtn = 0 End Select c.Offset(, -1) = rtn Next c End Sub Depending how many you need to do, selecting and looping each cell may be slow. -- Steve "tlee" wrote in message ... Hi AltaEgo, Thanks for your help. You give me the idea how to implement the macro. Besides, anyone know how to selected the specified column pattern for checking? Such that, check column (B, D, F, H, J, L,........) in the worksheet. I tried to find information about it. It is more likely to use "range.Offset( )" to implement. Am I correct? how can I loop it until checked all specified columns. Thanks for your in advance. Best regards, Tom Lee Sorry. I just noticed that you want to automate the process with code rather than use a function. Sub Test() ' Select column D cells ' Results show in Column C For Each c In Selection x = Len(c) Select Case x Case Is < 10 rtn = 5 Case Is < 20 rtn = 4 Case Is < 30 rtn = 3 Case Is < 40 rtn = 2 Case Is < 50 rtn = 1 Case Else rtn = 0 End Select c.Offset(, -1) = rtn Next c End Sub -- Steve "tlee" wrote in message ... Hi all, Could anyone help me how to check condition, and fill the data to the adjacent cell automatically? Such that, there are several columns at the worksheet. And I specified to use 2 columns (C and D) only. Column D contains characters in each cell. Column C is blank Column without data in all cell. It checks the number of the characters in each cell of column D. If the number of characters is 10 in D2, then fill "5" into C2 If the number of characters is 20 in D3, then fill "4" into C3 How can I implement that operation? Thanks for your in advance. TLee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill in date into adjacent cell automatically | Excel Discussion (Misc queries) | |||
How to select data in non adjacent rows of a column automatically | Excel Discussion (Misc queries) | |||
How to Automatically Move Cell datato adjacent cell.. | Excel Discussion (Misc queries) | |||
Loop through for condition and input in adjacent cell | Excel Programming | |||
automatically fill in a cell if the adjacent cell has any value | Excel Programming |