Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"currentcell.value" not working when numeric?
Hello there,
I've submitted this problem yesterday to the "excel.worksheets.functions" forum, but received only partial (although very helpful) response. Could you help me with the following issue (just to be sure how it works...)? -------------- Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric Does any of you have an idea how to modify the code in order to include also numeric values as possible variables? -------------- Many thanks! The entire code is as follows: Sub CopyRowsToSheets() 'copy rows to worksheets based on value in column A 'assume the worksheet name to paste to is the value in Col A Dim CurrentCell As Range Dim SourceRow As Range Dim Targetsht As Worksheet Dim TargetRow As Long Dim CurrentCellValue As String 'start with cell A2 on Sheet1 Set CurrentCell = Worksheets("sheet1").Cells(2, 1) 'row 2 column 1 Do While Not IsEmpty(CurrentCell) CurrentCellValue = CurrentCell.Value Set SourceRow = CurrentCell.EntireRow 'Check if worksheet exists On Error Resume Next Testwksht = Worksheets(CurrentCellValue).N*ame If Err.Number = 0 Then 'MsgBox CurrentCellValue & " worksheet Exists" Else MsgBox "Adding a new worksheet for " & CurrentCellValue Worksheets.Add.Name = CurrentCellValue End If On Error GoTo 0 'reset on error to trap errors again Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric ' Find next blank row in Targetsht - check using Column A TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row + 1 SourceRow.Copy Destination:=Targetsht.Cells(T*argetRow, 1) 'do the next cell Set CurrentCell = CurrentCell.Offset(1, 0) Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"currentcell.value" not working when numeric?
The 'Worksheets()' function allows you to select a worksheet by Name or bu
Number. In this last case, the number may *NOT* be higher than the number of sheets otherwise Excel triggers an error. If you do have worksheets that are also labelled as numbers, then use this trick: Set Targetsht = ActiveWorkbook.Worksheets("" & CurrĀ*entCellValue) This will transform the numerical value into a string and allow you to locate the sheet. Tip: always use the "Err" object to trap errors On Error Resume Next Err.Clear Set Targetsht = ActiveWorkbook.Worksheets("" & CurrĀ*entCellValue) If Err.Number < 0 Then MsgBox "Your error message here" Else {...whatever...} End If Dutch "markx" wrote: Hello there, I've submitted this problem yesterday to the "excel.worksheets.functions" forum, but received only partial (although very helpful) response. Could you help me with the following issue (just to be sure how it works...)? -------------- Set Targetsht = ActiveWorkbook.Worksheets(CurrĀ*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric Does any of you have an idea how to modify the code in order to include also numeric values as possible variables? -------------- Many thanks! The entire code is as follows: Sub CopyRowsToSheets() 'copy rows to worksheets based on value in column A 'assume the worksheet name to paste to is the value in Col A Dim CurrentCell As Range Dim SourceRow As Range Dim Targetsht As Worksheet Dim TargetRow As Long Dim CurrentCellValue As String 'start with cell A2 on Sheet1 Set CurrentCell = Worksheets("sheet1").Cells(2, 1) 'row 2 column 1 Do While Not IsEmpty(CurrentCell) CurrentCellValue = CurrentCell.Value Set SourceRow = CurrentCell.EntireRow 'Check if worksheet exists On Error Resume Next Testwksht = Worksheets(CurrentCellValue).NĀ*ame If Err.Number = 0 Then 'MsgBox CurrentCellValue & " worksheet Exists" Else MsgBox "Adding a new worksheet for " & CurrentCellValue Worksheets.Add.Name = CurrentCellValue End If On Error GoTo 0 'reset on error to trap errors again Set Targetsht = ActiveWorkbook.Worksheets(CurrĀ*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric ' Find next blank row in Targetsht - check using Column A TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row + 1 SourceRow.Copy Destination:=Targetsht.Cells(TĀ*argetRow, 1) 'do the next cell Set CurrentCell = CurrentCell.Offset(1, 0) Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"currentcell.value" not working when numeric?
mark,
I assume you are attempting to reference e.g ActiveWorkbook.Worksheets(1) but Curr*entCellValue="1" (a string), not 1 (a number). So unless you have a sheet with the name of "1" on its tab, it will fail. NickHK "markx" wrote in message ... Hello there, I've submitted this problem yesterday to the "excel.worksheets.functions" forum, but received only partial (although very helpful) response. Could you help me with the following issue (just to be sure how it works...)? -------------- Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric Does any of you have an idea how to modify the code in order to include also numeric values as possible variables? -------------- Many thanks! The entire code is as follows: Sub CopyRowsToSheets() 'copy rows to worksheets based on value in column A 'assume the worksheet name to paste to is the value in Col A Dim CurrentCell As Range Dim SourceRow As Range Dim Targetsht As Worksheet Dim TargetRow As Long Dim CurrentCellValue As String 'start with cell A2 on Sheet1 Set CurrentCell = Worksheets("sheet1").Cells(2, 1) 'row 2 column 1 Do While Not IsEmpty(CurrentCell) CurrentCellValue = CurrentCell.Value Set SourceRow = CurrentCell.EntireRow 'Check if worksheet exists On Error Resume Next Testwksht = Worksheets(CurrentCellValue).N*ame If Err.Number = 0 Then 'MsgBox CurrentCellValue & " worksheet Exists" Else MsgBox "Adding a new worksheet for " & CurrentCellValue Worksheets.Add.Name = CurrentCellValue End If On Error GoTo 0 'reset on error to trap errors again Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric ' Find next blank row in Targetsht - check using Column A TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row 1 SourceRow.Copy Destination:=Targetsht.Cells(T*argetRow, 1) 'do the next cell Set CurrentCell = CurrentCell.Offset(1, 0) Loop End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"currentcell.value" not working when numeric? = Didn't get it...
Thank you both for your comments, but I think I'm not as good in VBA
programming as you maybe think... and are not quite sure how to interpret your input. I'll try to put in place the solution proposed by Dutch and get back to you later, but as far as I can understand it, it will only work if the sheets ARE ALREADY CREATED. So, how should I proceed if I there are no other worksheets in place (the only one being the "source" sheet)? (just to recall: for the time being the code creates normally all the sheets it needs and then puts all the relevant data in place UNLESS the criteria/variables are "numeric"). What would be the best solution to overcome this limitation? Thanks once again for your help (and sorry if you already answered the question, but I was *unable* to get it:-D) Mark "NickHK" wrote in message ... mark, I assume you are attempting to reference e.g ActiveWorkbook.Worksheets(1) but Curr*entCellValue="1" (a string), not 1 (a number). So unless you have a sheet with the name of "1" on its tab, it will fail. NickHK "markx" wrote in message ... Hello there, I've submitted this problem yesterday to the "excel.worksheets.functions" forum, but received only partial (although very helpful) response. Could you help me with the following issue (just to be sure how it works...)? -------------- Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric Does any of you have an idea how to modify the code in order to include also numeric values as possible variables? -------------- Many thanks! The entire code is as follows: Sub CopyRowsToSheets() 'copy rows to worksheets based on value in column A 'assume the worksheet name to paste to is the value in Col A Dim CurrentCell As Range Dim SourceRow As Range Dim Targetsht As Worksheet Dim TargetRow As Long Dim CurrentCellValue As String 'start with cell A2 on Sheet1 Set CurrentCell = Worksheets("sheet1").Cells(2, 1) 'row 2 column 1 Do While Not IsEmpty(CurrentCell) CurrentCellValue = CurrentCell.Value Set SourceRow = CurrentCell.EntireRow 'Check if worksheet exists On Error Resume Next Testwksht = Worksheets(CurrentCellValue).N*ame If Err.Number = 0 Then 'MsgBox CurrentCellValue & " worksheet Exists" Else MsgBox "Adding a new worksheet for " & CurrentCellValue Worksheets.Add.Name = CurrentCellValue End If On Error GoTo 0 'reset on error to trap errors again Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric ' Find next blank row in Targetsht - check using Column A TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row 1 SourceRow.Copy Destination:=Targetsht.Cells(T*argetRow, 1) 'do the next cell Set CurrentCell = CurrentCell.Offset(1, 0) Loop End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"currentcell.value" not working when numeric? = Didn't get it...
Mark,
So you want create a new worksheet ? Set Targetsht = ActiveWorkbook.Worksheets.Add Targetsht.Name="Whatever" NickHK "markx" wrote in message ... Thank you both for your comments, but I think I'm not as good in VBA programming as you maybe think... and are not quite sure how to interpret your input. I'll try to put in place the solution proposed by Dutch and get back to you later, but as far as I can understand it, it will only work if the sheets ARE ALREADY CREATED. So, how should I proceed if I there are no other worksheets in place (the only one being the "source" sheet)? (just to recall: for the time being the code creates normally all the sheets it needs and then puts all the relevant data in place UNLESS the criteria/variables are "numeric"). What would be the best solution to overcome this limitation? Thanks once again for your help (and sorry if you already answered the question, but I was *unable* to get it:-D) Mark "NickHK" wrote in message ... mark, I assume you are attempting to reference e.g ActiveWorkbook.Worksheets(1) but Curr*entCellValue="1" (a string), not 1 (a number). So unless you have a sheet with the name of "1" on its tab, it will fail. NickHK "markx" wrote in message ... Hello there, I've submitted this problem yesterday to the "excel.worksheets.functions" forum, but received only partial (although very helpful) response. Could you help me with the following issue (just to be sure how it works...)? -------------- Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric Does any of you have an idea how to modify the code in order to include also numeric values as possible variables? -------------- Many thanks! The entire code is as follows: Sub CopyRowsToSheets() 'copy rows to worksheets based on value in column A 'assume the worksheet name to paste to is the value in Col A Dim CurrentCell As Range Dim SourceRow As Range Dim Targetsht As Worksheet Dim TargetRow As Long Dim CurrentCellValue As String 'start with cell A2 on Sheet1 Set CurrentCell = Worksheets("sheet1").Cells(2, 1) 'row 2 column 1 Do While Not IsEmpty(CurrentCell) CurrentCellValue = CurrentCell.Value Set SourceRow = CurrentCell.EntireRow 'Check if worksheet exists On Error Resume Next Testwksht = Worksheets(CurrentCellValue).N*ame If Err.Number = 0 Then 'MsgBox CurrentCellValue & " worksheet Exists" Else MsgBox "Adding a new worksheet for " & CurrentCellValue Worksheets.Add.Name = CurrentCellValue End If On Error GoTo 0 'reset on error to trap errors again Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric ' Find next blank row in Targetsht - check using Column A TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row 1 SourceRow.Copy Destination:=Targetsht.Cells(T*argetRow, 1) 'do the next cell Set CurrentCell = CurrentCell.Offset(1, 0) Loop End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"currentcell.value" not working when numeric? = ...
Thanks Nick,
Yes, I would like to have all the worksheets created, but with the names that are taken from the relevant column from the "source" page. It means that if in our column we have the following categories: "Apples", "Bananas", "Pears", then I would like to have 3 new pages created with the names "Apples", "Bananas", "Pears" and then filled with the corresponding fields. My problem is that if the labels (categories) are not names (like "Apples", "Bananas", "Pears"), but numbers (like "1", "5", "123"), the code gives me the error in the line Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue) <for the full code see below My goal would be to find a solution to modify the code in such a way that it will overcome this limitation and will add/fill the pages with the necessary data irrespective of the variable type (i.e. either text or numeric). Thanks again for all your feedback! Mark "NickHK" wrote in message ... Mark, So you want create a new worksheet ? Set Targetsht = ActiveWorkbook.Worksheets.Add Targetsht.Name="Whatever" NickHK "markx" wrote in message ... Thank you both for your comments, but I think I'm not as good in VBA programming as you maybe think... and are not quite sure how to interpret your input. I'll try to put in place the solution proposed by Dutch and get back to you later, but as far as I can understand it, it will only work if the sheets ARE ALREADY CREATED. So, how should I proceed if I there are no other worksheets in place (the only one being the "source" sheet)? (just to recall: for the time being the code creates normally all the sheets it needs and then puts all the relevant data in place UNLESS the criteria/variables are "numeric"). What would be the best solution to overcome this limitation? Thanks once again for your help (and sorry if you already answered the question, but I was *unable* to get it:-D) Mark "NickHK" wrote in message ... mark, I assume you are attempting to reference e.g ActiveWorkbook.Worksheets(1) but Curr*entCellValue="1" (a string), not 1 (a number). So unless you have a sheet with the name of "1" on its tab, it will fail. NickHK "markx" wrote in message ... Hello there, I've submitted this problem yesterday to the "excel.worksheets.functions" forum, but received only partial (although very helpful) response. Could you help me with the following issue (just to be sure how it works...)? -------------- Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric Does any of you have an idea how to modify the code in order to include also numeric values as possible variables? -------------- Many thanks! The entire code is as follows: Sub CopyRowsToSheets() 'copy rows to worksheets based on value in column A 'assume the worksheet name to paste to is the value in Col A Dim CurrentCell As Range Dim SourceRow As Range Dim Targetsht As Worksheet Dim TargetRow As Long Dim CurrentCellValue As String 'start with cell A2 on Sheet1 Set CurrentCell = Worksheets("sheet1").Cells(2, 1) 'row 2 column 1 Do While Not IsEmpty(CurrentCell) CurrentCellValue = CurrentCell.Value Set SourceRow = CurrentCell.EntireRow 'Check if worksheet exists On Error Resume Next Testwksht = Worksheets(CurrentCellValue).N*ame If Err.Number = 0 Then 'MsgBox CurrentCellValue & " worksheet Exists" Else MsgBox "Adding a new worksheet for " & CurrentCellValue Worksheets.Add.Name = CurrentCellValue End If On Error GoTo 0 'reset on error to trap errors again Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric ' Find next blank row in Targetsht - check using Column A TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row 1 SourceRow.Copy Destination:=Targetsht.Cells(T*argetRow, 1) 'do the next cell Set CurrentCell = CurrentCell.Offset(1, 0) Loop End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"currentcell.value" not working when numeric? = ...
Mark,
Code works for me. What error do you get ? NickHK "markx" wrote in message ... Thanks Nick, Yes, I would like to have all the worksheets created, but with the names that are taken from the relevant column from the "source" page. It means that if in our column we have the following categories: "Apples", "Bananas", "Pears", then I would like to have 3 new pages created with the names "Apples", "Bananas", "Pears" and then filled with the corresponding fields. My problem is that if the labels (categories) are not names (like "Apples", "Bananas", "Pears"), but numbers (like "1", "5", "123"), the code gives me the error in the line Set Targetsht = ActiveWorkbook.Worksheets(Curr*entCellValue) <for the full code see below My goal would be to find a solution to modify the code in such a way that it will overcome this limitation and will add/fill the pages with the necessary data irrespective of the variable type (i.e. either text or numeric). Thanks again for all your feedback! Mark ------------------ CUT ---------------------- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"currentcell.value" not working when numeric? = ...
In your code I found this statement:
{...} Else MsgBox "Adding a new worksheet for " & CurrentCellValue Worksheets.Add.Name = CurrentCellValue End If {...} It is incorrect. It should be Dim wNewSheet As Worksheet {...} Else MsgBox "Adding a new worksheet for " & CurrentCellValue Set wNewSheet = Workbook.Sheets.Add ' {additional stements here} With wNewSheet .Name = "" & CurrentCellValue ' assign "Title" of sheet here ' {other statements here, for instance using .Move to move it at the end} End With End If {...} I would also: Dim CurrentCellValue As Variant instead of Dim CurrentCellValue As String to take care of Excel's habit of self-determining the cell's data type Dutch "markx" wrote: Thanks Nick, Yes, I would like to have all the worksheets created, but with the names that are taken from the relevant column from the "source" page. It means that if in our column we have the following categories: "Apples", "Bananas", "Pears", then I would like to have 3 new pages created with the names "Apples", "Bananas", "Pears" and then filled with the corresponding fields. My problem is that if the labels (categories) are not names (like "Apples", "Bananas", "Pears"), but numbers (like "1", "5", "123"), the code gives me the error in the line Set Targetsht = ActiveWorkbook.Worksheets(CurrĀ*entCellValue) <for the full code see below My goal would be to find a solution to modify the code in such a way that it will overcome this limitation and will add/fill the pages with the necessary data irrespective of the variable type (i.e. either text or numeric). Thanks again for all your feedback! Mark "NickHK" wrote in message ... Mark, So you want create a new worksheet ? Set Targetsht = ActiveWorkbook.Worksheets.Add Targetsht.Name="Whatever" NickHK "markx" wrote in message ... Thank you both for your comments, but I think I'm not as good in VBA programming as you maybe think... and are not quite sure how to interpret your input. I'll try to put in place the solution proposed by Dutch and get back to you later, but as far as I can understand it, it will only work if the sheets ARE ALREADY CREATED. So, how should I proceed if I there are no other worksheets in place (the only one being the "source" sheet)? (just to recall: for the time being the code creates normally all the sheets it needs and then puts all the relevant data in place UNLESS the criteria/variables are "numeric"). What would be the best solution to overcome this limitation? Thanks once again for your help (and sorry if you already answered the question, but I was *unable* to get it:-D) Mark "NickHK" wrote in message ... mark, I assume you are attempting to reference e.g ActiveWorkbook.Worksheets(1) but CurrĀ*entCellValue="1" (a string), not 1 (a number). So unless you have a sheet with the name of "1" on its tab, it will fail. NickHK "markx" wrote in message ... Hello there, I've submitted this problem yesterday to the "excel.worksheets.functions" forum, but received only partial (although very helpful) response. Could you help me with the following issue (just to be sure how it works...)? -------------- Set Targetsht = ActiveWorkbook.Worksheets(CurrĀ*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric Does any of you have an idea how to modify the code in order to include also numeric values as possible variables? -------------- Many thanks! The entire code is as follows: Sub CopyRowsToSheets() 'copy rows to worksheets based on value in column A 'assume the worksheet name to paste to is the value in Col A Dim CurrentCell As Range Dim SourceRow As Range Dim Targetsht As Worksheet Dim TargetRow As Long Dim CurrentCellValue As String 'start with cell A2 on Sheet1 Set CurrentCell = Worksheets("sheet1").Cells(2, 1) 'row 2 column 1 Do While Not IsEmpty(CurrentCell) CurrentCellValue = CurrentCell.Value Set SourceRow = CurrentCell.EntireRow 'Check if worksheet exists On Error Resume Next Testwksht = Worksheets(CurrentCellValue).NĀ*ame If Err.Number = 0 Then 'MsgBox CurrentCellValue & " worksheet Exists" Else MsgBox "Adding a new worksheet for " & CurrentCellValue Worksheets.Add.Name = CurrentCellValue End If On Error GoTo 0 'reset on error to trap errors again Set Targetsht = ActiveWorkbook.Worksheets(CurrĀ*entCellValue) 'note: using CurrentCell.value gave me an error if the value was numeric ' Find next blank row in Targetsht - check using Column A TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row 1 SourceRow.Copy Destination:=Targetsht.Cells(TĀ*argetRow, 1) 'do the next cell Set CurrentCell = CurrentCell.Offset(1, 0) Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
"." on numeric keypad displays "," | Excel Discussion (Misc queries) | |||
Excel: Changing "numeric $" to "text $" in a different cell. | Excel Worksheet Functions | |||
If A3=alpha numeric,"X", if A3=text,"Y", Blank | Excel Worksheet Functions | |||
Working out age from "Day" "Month" "Year" | Excel Worksheet Functions |