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! |
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 |