| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.column
In the range M5:AJ5 I identify the column as "Actual" or "Forecast".
As I loop through the rows, I need to copy only the columns with actual data and paste it into another worksheet. I was able to identify the start column and end column of the actual data, however the '.column' function gives me the address in R1C1 (eg. 13 through 24). Is there a way to get the column in Local (M through X)? Probably easy but.... Thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
.column
EWR,
When you have the column number, you can get the letter by using the function below. Use it like myLetter = ColLet(columnNumber) But there is never any reason to use the column letter in VBA. Perhaps you could post your code where you think you need a column letter. HTH, Bernie MS Excel MVP Function ColLet(ColNum As Integer) As String If ColNum 26 Then ColLet = Chr((ColNum - 1) \ 26 + 64) ColLet = ColLet & Chr(((ColNum - 1) Mod 26) + 65) End Function "EWR" wrote in message ... In the range M5:AJ5 I identify the column as "Actual" or "Forecast". As I loop through the rows, I need to copy only the columns with actual data and paste it into another worksheet. I was able to identify the start column and end column of the actual data, however the '.column' function gives me the address in R1C1 (eg. 13 through 24). Is there a way to get the column in Local (M through X)? Probably easy but.... Thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
.column
Sorry...I am no expert here...
'get actual range Sheets(wrk1).Select Range("M5").Select val = ActiveCell.Value 'Set begin column While ActiveCell.Value < "Actual" And i < 25 ActiveCell.Offset(0, 1).Select i = i + 1 Wend beg = ActiveCell.Column i = i + 1 ActiveCell.Offset(0, 1).Select i = i + 1 'Set end column While ActiveCell.Value = "Actual" And i < 25 ActiveCell.Offset(0, 1).Select i = i + 1 Wend end_col = ActiveCell.Column - 1 'MsgBox beg & " " & end_col ....Loop through rows, find location... 'get range of actuals act_row =activecell.row bcell = "R" & act_row & "C" & beg_acts ecell = "R" & act_row & "C" & end_acts rng = bcell & ":" & ecell Range(rng).Select "Bernie Deitrick" wrote: EWR, When you have the column number, you can get the letter by using the function below. Use it like myLetter = ColLet(columnNumber) But there is never any reason to use the column letter in VBA. Perhaps you could post your code where you think you need a column letter. HTH, Bernie MS Excel MVP Function ColLet(ColNum As Integer) As String If ColNum 26 Then ColLet = Chr((ColNum - 1) \ 26 + 64) ColLet = ColLet & Chr(((ColNum - 1) Mod 26) + 65) End Function "EWR" wrote in message ... In the range M5:AJ5 I identify the column as "Actual" or "Forecast". As I loop through the rows, I need to copy only the columns with actual data and paste it into another worksheet. I was able to identify the start column and end column of the actual data, however the '.column' function gives me the address in R1C1 (eg. 13 through 24). Is there a way to get the column in Local (M through X)? Probably easy but.... Thanks! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
.column
EWR,
Change MsgBox beg & " " & end_col to MsgBox "The beginning column is " & ColLet(beg) & Chr(10) & _ "and the end column is " & ColLet(end_Col) Assumes beg and end_col are declared integer values. If not, use MsgBox "The beginning column is " & ColLet(CInt(beg)) & Chr(10) & _ "and the end column is " & ColLet(CInt(end_Col)) HTH, Bernie MS Excel MVP "EWR" wrote in message ... Sorry...I am no expert here... 'get actual range Sheets(wrk1).Select Range("M5").Select val = ActiveCell.Value 'Set begin column While ActiveCell.Value < "Actual" And i < 25 ActiveCell.Offset(0, 1).Select i = i + 1 Wend beg = ActiveCell.Column i = i + 1 ActiveCell.Offset(0, 1).Select i = i + 1 'Set end column While ActiveCell.Value = "Actual" And i < 25 ActiveCell.Offset(0, 1).Select i = i + 1 Wend end_col = ActiveCell.Column - 1 'MsgBox beg & " " & end_col ...Loop through rows, find location... 'get range of actuals act_row =activecell.row bcell = "R" & act_row & "C" & beg_acts ecell = "R" & act_row & "C" & end_acts rng = bcell & ":" & ecell Range(rng).Select "Bernie Deitrick" wrote: EWR, When you have the column number, you can get the letter by using the function below. Use it like myLetter = ColLet(columnNumber) But there is never any reason to use the column letter in VBA. Perhaps you could post your code where you think you need a column letter. HTH, Bernie MS Excel MVP Function ColLet(ColNum As Integer) As String If ColNum 26 Then ColLet = Chr((ColNum - 1) \ 26 + 64) ColLet = ColLet & Chr(((ColNum - 1) Mod 26) + 65) End Function "EWR" wrote in message ... In the range M5:AJ5 I identify the column as "Actual" or "Forecast". As I loop through the rows, I need to copy only the columns with actual data and paste it into another worksheet. I was able to identify the start column and end column of the actual data, however the '.column' function gives me the address in R1C1 (eg. 13 through 24). Is there a way to get the column in Local (M through X)? Probably easy but.... Thanks! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
.column
Thanks a ton Bernie!
Since I am trying to learn, you mentioned << But there is never any reason to use the column letter in VBA. Perhaps you could post your code where you think you need a column letter. Is there an alternative way I can accomplish this without using the letter? "Bernie Deitrick" wrote: EWR, Change MsgBox beg & " " & end_col to MsgBox "The beginning column is " & ColLet(beg) & Chr(10) & _ "and the end column is " & ColLet(end_Col) Assumes beg and end_col are declared integer values. If not, use MsgBox "The beginning column is " & ColLet(CInt(beg)) & Chr(10) & _ "and the end column is " & ColLet(CInt(end_Col)) HTH, Bernie MS Excel MVP "EWR" wrote in message ... Sorry...I am no expert here... 'get actual range Sheets(wrk1).Select Range("M5").Select val = ActiveCell.Value 'Set begin column While ActiveCell.Value < "Actual" And i < 25 ActiveCell.Offset(0, 1).Select i = i + 1 Wend beg = ActiveCell.Column i = i + 1 ActiveCell.Offset(0, 1).Select i = i + 1 'Set end column While ActiveCell.Value = "Actual" And i < 25 ActiveCell.Offset(0, 1).Select i = i + 1 Wend end_col = ActiveCell.Column - 1 'MsgBox beg & " " & end_col ...Loop through rows, find location... 'get range of actuals act_row =activecell.row bcell = "R" & act_row & "C" & beg_acts ecell = "R" & act_row & "C" & end_acts rng = bcell & ":" & ecell Range(rng).Select "Bernie Deitrick" wrote: EWR, When you have the column number, you can get the letter by using the function below. Use it like myLetter = ColLet(columnNumber) But there is never any reason to use the column letter in VBA. Perhaps you could post your code where you think you need a column letter. HTH, Bernie MS Excel MVP Function ColLet(ColNum As Integer) As String If ColNum 26 Then ColLet = Chr((ColNum - 1) \ 26 + 64) ColLet = ColLet & Chr(((ColNum - 1) Mod 26) + 65) End Function "EWR" wrote in message ... In the range M5:AJ5 I identify the column as "Actual" or "Forecast". As I loop through the rows, I need to copy only the columns with actual data and paste it into another worksheet. I was able to identify the start column and end column of the actual data, however the '.column' function gives me the address in R1C1 (eg. 13 through 24). Is there a way to get the column in Local (M through X)? Probably easy but.... Thanks! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
.column
EWR,
Since you are communicating with the user, column letters become necessary. You could come close with this: MsgBox "The beginning column is " & _ Columns(beg).Address(False, False) _ & Chr(10) & "and the end column is " & _ Columns(end_col).Address(False, False) HTH, Bernie MS Excel MVP "EWR" wrote in message ... Thanks a ton Bernie! Since I am trying to learn, you mentioned << But there is never any reason to use the column letter in VBA. Perhaps you could post your code where you think you need a column letter. Is there an alternative way I can accomplish this without using the letter? "Bernie Deitrick" wrote: EWR, Change MsgBox beg & " " & end_col to MsgBox "The beginning column is " & ColLet(beg) & Chr(10) & _ "and the end column is " & ColLet(end_Col) Assumes beg and end_col are declared integer values. If not, use MsgBox "The beginning column is " & ColLet(CInt(beg)) & Chr(10) & _ "and the end column is " & ColLet(CInt(end_Col)) HTH, Bernie MS Excel MVP "EWR" wrote in message ... Sorry...I am no expert here... 'get actual range Sheets(wrk1).Select Range("M5").Select val = ActiveCell.Value 'Set begin column While ActiveCell.Value < "Actual" And i < 25 ActiveCell.Offset(0, 1).Select i = i + 1 Wend beg = ActiveCell.Column i = i + 1 ActiveCell.Offset(0, 1).Select i = i + 1 'Set end column While ActiveCell.Value = "Actual" And i < 25 ActiveCell.Offset(0, 1).Select i = i + 1 Wend end_col = ActiveCell.Column - 1 'MsgBox beg & " " & end_col ...Loop through rows, find location... 'get range of actuals act_row =activecell.row bcell = "R" & act_row & "C" & beg_acts ecell = "R" & act_row & "C" & end_acts rng = bcell & ":" & ecell Range(rng).Select "Bernie Deitrick" wrote: EWR, When you have the column number, you can get the letter by using the function below. Use it like myLetter = ColLet(columnNumber) But there is never any reason to use the column letter in VBA. Perhaps you could post your code where you think you need a column letter. HTH, Bernie MS Excel MVP Function ColLet(ColNum As Integer) As String If ColNum 26 Then ColLet = Chr((ColNum - 1) \ 26 + 64) ColLet = ColLet & Chr(((ColNum - 1) Mod 26) + 65) End Function "EWR" wrote in message ... In the range M5:AJ5 I identify the column as "Actual" or "Forecast". As I loop through the rows, I need to copy only the columns with actual data and paste it into another worksheet. I was able to identify the start column and end column of the actual data, however the '.column' function gives me the address in R1C1 (eg. 13 through 24). Is there a way to get the column in Local (M through X)? Probably easy but.... Thanks! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
.column
Bernie,
Thanks again. Sorry to mislead, but the msgbox was there for me only...shoud have taken it out. The user never needs to know what the columns are, I just need the columns to identify the start and end of the range. "Bernie Deitrick" wrote: EWR, Since you are communicating with the user, column letters become necessary. You could come close with this: MsgBox "The beginning column is " & _ Columns(beg).Address(False, False) _ & Chr(10) & "and the end column is " & _ Columns(end_col).Address(False, False) HTH, Bernie MS Excel MVP "EWR" wrote in message ... Thanks a ton Bernie! Since I am trying to learn, you mentioned << But there is never any reason to use the column letter in VBA. Perhaps you could post your code where you think you need a column letter. Is there an alternative way I can accomplish this without using the letter? "Bernie Deitrick" wrote: EWR, Change MsgBox beg & " " & end_col to MsgBox "The beginning column is " & ColLet(beg) & Chr(10) & _ "and the end column is " & ColLet(end_Col) Assumes beg and end_col are declared integer values. If not, use MsgBox "The beginning column is " & ColLet(CInt(beg)) & Chr(10) & _ "and the end column is " & ColLet(CInt(end_Col)) HTH, Bernie MS Excel MVP "EWR" wrote in message ... Sorry...I am no expert here... 'get actual range Sheets(wrk1).Select Range("M5").Select val = ActiveCell.Value 'Set begin column While ActiveCell.Value < "Actual" And i < 25 ActiveCell.Offset(0, 1).Select i = i + 1 Wend beg = ActiveCell.Column i = i + 1 ActiveCell.Offset(0, 1).Select i = i + 1 'Set end column While ActiveCell.Value = "Actual" And i < 25 ActiveCell.Offset(0, 1).Select i = i + 1 Wend end_col = ActiveCell.Column - 1 'MsgBox beg & " " & end_col ...Loop through rows, find location... 'get range of actuals act_row =activecell.row bcell = "R" & act_row & "C" & beg_acts ecell = "R" & act_row & "C" & end_acts rng = bcell & ":" & ecell Range(rng).Select "Bernie Deitrick" wrote: EWR, When you have the column number, you can get the letter by using the function below. Use it like myLetter = ColLet(columnNumber) But there is never any reason to use the column letter in VBA. Perhaps you could post your code where you think you need a column letter. HTH, Bernie MS Excel MVP Function ColLet(ColNum As Integer) As String If ColNum 26 Then ColLet = Chr((ColNum - 1) \ 26 + 64) ColLet = ColLet & Chr(((ColNum - 1) Mod 26) + 65) End Function "EWR" wrote in message ... In the range M5:AJ5 I identify the column as "Actual" or "Forecast". As I loop through the rows, I need to copy only the columns with actual data and paste it into another worksheet. I was able to identify the start column and end column of the actual data, however the '.column' function gives me the address in R1C1 (eg. 13 through 24). Is there a way to get the column in Local (M through X)? Probably easy but.... Thanks! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
.column
EWR,
Sorry. Where you have: act_row =activecell.row bcell = "R" & act_row & "C" & beg_acts ecell = "R" & act_row & "C" & end_acts rng = bcell & ":" & ecell Range(rng).Select You could simply use act_row =activecell.row Range(Cells(act_row,beg_acts),Cells(act_row,end_ac ts)).Select HTH, Bernie MS Excel MVP "EWR" wrote in message ... Bernie, Thanks again. Sorry to mislead, but the msgbox was there for me only...shoud have taken it out. The user never needs to know what the columns are, I just need the columns to identify the start and end of the range. "Bernie Deitrick" wrote: EWR, Since you are communicating with the user, column letters become necessary. You could come close with this: MsgBox "The beginning column is " & _ Columns(beg).Address(False, False) _ & Chr(10) & "and the end column is " & _ Columns(end_col).Address(False, False) HTH, Bernie MS Excel MVP "EWR" wrote in message ... Thanks a ton Bernie! Since I am trying to learn, you mentioned << But there is never any reason to use the column letter in VBA. Perhaps you could post your code where you think you need a column letter. Is there an alternative way I can accomplish this without using the letter? "Bernie Deitrick" wrote: EWR, Change MsgBox beg & " " & end_col to MsgBox "The beginning column is " & ColLet(beg) & Chr(10) & _ "and the end column is " & ColLet(end_Col) Assumes beg and end_col are declared integer values. If not, use MsgBox "The beginning column is " & ColLet(CInt(beg)) & Chr(10) & _ "and the end column is " & ColLet(CInt(end_Col)) HTH, Bernie MS Excel MVP "EWR" wrote in message ... Sorry...I am no expert here... 'get actual range Sheets(wrk1).Select Range("M5").Select val = ActiveCell.Value 'Set begin column While ActiveCell.Value < "Actual" And i < 25 ActiveCell.Offset(0, 1).Select i = i + 1 Wend beg = ActiveCell.Column i = i + 1 ActiveCell.Offset(0, 1).Select i = i + 1 'Set end column While ActiveCell.Value = "Actual" And i < 25 ActiveCell.Offset(0, 1).Select i = i + 1 Wend end_col = ActiveCell.Column - 1 'MsgBox beg & " " & end_col ...Loop through rows, find location... 'get range of actuals act_row =activecell.row bcell = "R" & act_row & "C" & beg_acts ecell = "R" & act_row & "C" & end_acts rng = bcell & ":" & ecell Range(rng).Select "Bernie Deitrick" wrote: EWR, When you have the column number, you can get the letter by using the function below. Use it like myLetter = ColLet(columnNumber) But there is never any reason to use the column letter in VBA. Perhaps you could post your code where you think you need a column letter. HTH, Bernie MS Excel MVP Function ColLet(ColNum As Integer) As String If ColNum 26 Then ColLet = Chr((ColNum - 1) \ 26 + 64) ColLet = ColLet & Chr(((ColNum - 1) Mod 26) + 65) End Function "EWR" wrote in message ... In the range M5:AJ5 I identify the column as "Actual" or "Forecast". As I loop through the rows, I need to copy only the columns with actual data and paste it into another worksheet. I was able to identify the start column and end column of the actual data, however the '.column' function gives me the address in R1C1 (eg. 13 through 24). Is there a way to get the column in Local (M through X)? Probably easy but.... Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| divide column(x) by column(y) to give column(x/y) in excel? | New Users to Excel | |||
| Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
| Return text in Column A if Column B and Column K match | Excel Worksheet Functions | |||
| Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
| Divide Column A by Column B multiply Column C | Excel Worksheet Functions | |||
Linear Mode
