|
.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! |
.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! |
.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! |
.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! |
.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! |
.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! |
.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! |
.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! |
| All times are GMT +1. The time now is 05:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com