Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing value in cell in another sheet
I am trying to write to a cell in another sheet in the same workbook using
the following code: strRow = "A" + CStr(nTempRow) With Worksheets("Temp") strTemp = "A" + CStr(nRow) .Range(strRow).Value = strTemp strTemp = "B" + CStr(nTempRow) .Range(strRow).Value = 0 End With When the code gets to fourth row, it bums out (skips to last function in module). The worksheet("Temp") is physically located as the first worksheet in the workbook. I have also used its number ("Sheet10"), but the same thing happens. The above With is within another With (have also tried this using Worksheet("Temp").Range(strRow).Value = ... In the Temp worksheet, Column A is formatted as General, Row B as integer Thanks, John H W |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing value in cell in another sheet
John,
You are assigning a value to a variable which you then write to a cell which is then immediately over-written with a zero: Seems like you would actually want to increment strRow, not strTemp. strTemp = "A" + CStr(nRow) .Range(strRow).Value = strTemp strTemp = "B" + CStr(nTempRow) .Range(strRow).Value = 0 HTH, Bernie MS Excel MVP "John H W" wrote in message ... I am trying to write to a cell in another sheet in the same workbook using the following code: strRow = "A" + CStr(nTempRow) With Worksheets("Temp") strTemp = "A" + CStr(nRow) .Range(strRow).Value = strTemp strTemp = "B" + CStr(nTempRow) .Range(strRow).Value = 0 End With When the code gets to fourth row, it bums out (skips to last function in module). The worksheet("Temp") is physically located as the first worksheet in the workbook. I have also used its number ("Sheet10"), but the same thing happens. The above With is within another With (have also tried this using Worksheet("Temp").Range(strRow).Value = ... In the Temp worksheet, Column A is formatted as General, Row B as integer Thanks, John H W |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing value in cell in another sheet
Bernie:
nTempRow is the row in sheet "Temp." I did have a typo, i.e., the line ..Range(strRow).Value=0 should have been .Range(strTemp).Value=0. When I corrected typo, it still kicks out on the line: .Range(strRow.Value = strTemp Thanks, John "Bernie Deitrick" wrote: John, You are assigning a value to a variable which you then write to a cell which is then immediately over-written with a zero: Seems like you would actually want to increment strRow, not strTemp. strTemp = "A" + CStr(nRow) .Range(strRow).Value = strTemp strTemp = "B" + CStr(nTempRow) .Range(strRow).Value = 0 HTH, Bernie MS Excel MVP "John H W" wrote in message ... I am trying to write to a cell in another sheet in the same workbook using the following code: strRow = "A" + CStr(nTempRow) With Worksheets("Temp") strTemp = "A" + CStr(nRow) .Range(strRow).Value = strTemp strTemp = "B" + CStr(nTempRow) .Range(strRow).Value = 0 End With When the code gets to fourth row, it bums out (skips to last function in module). The worksheet("Temp") is physically located as the first worksheet in the workbook. I have also used its number ("Sheet10"), but the same thing happens. The above With is within another With (have also tried this using Worksheet("Temp").Range(strRow).Value = ... In the Temp worksheet, Column A is formatted as General, Row B as integer Thanks, John H W |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing value in cell in another sheet
John,
When it kicks out, put your cursor on the variable strRow (or add a watch). What is the value of that strRow? It should be "A" followed by a number, but if the number is zero or less, or greater than 65536, that will give an error. HTH, Bernie MS Excel MVP "John H W" wrote in message ... Bernie: nTempRow is the row in sheet "Temp." I did have a typo, i.e., the line .Range(strRow).Value=0 should have been .Range(strTemp).Value=0. When I corrected typo, it still kicks out on the line: .Range(strRow.Value = strTemp Thanks, John "Bernie Deitrick" wrote: John, You are assigning a value to a variable which you then write to a cell which is then immediately over-written with a zero: Seems like you would actually want to increment strRow, not strTemp. strTemp = "A" + CStr(nRow) .Range(strRow).Value = strTemp strTemp = "B" + CStr(nTempRow) .Range(strRow).Value = 0 HTH, Bernie MS Excel MVP "John H W" wrote in message ... I am trying to write to a cell in another sheet in the same workbook using the following code: strRow = "A" + CStr(nTempRow) With Worksheets("Temp") strTemp = "A" + CStr(nRow) .Range(strRow).Value = strTemp strTemp = "B" + CStr(nTempRow) .Range(strRow).Value = 0 End With When the code gets to fourth row, it bums out (skips to last function in module). The worksheet("Temp") is physically located as the first worksheet in the workbook. I have also used its number ("Sheet10"), but the same thing happens. The above With is within another With (have also tried this using Worksheet("Temp").Range(strRow).Value = ... In the Temp worksheet, Column A is formatted as General, Row B as integer Thanks, John H W |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing value in cell in another sheet
Bernie:
I checked the contents of strRow (and strTemp) "before" I run the line. Can't do it after as control has transferred to the last function (although nowhere have I called it) in the module and the function where this is located is out of scope. John "Bernie Deitrick" wrote: John, When it kicks out, put your cursor on the variable strRow (or add a watch). What is the value of that strRow? It should be "A" followed by a number, but if the number is zero or less, or greater than 65536, that will give an error. HTH, Bernie MS Excel MVP "John H W" wrote in message ... Bernie: nTempRow is the row in sheet "Temp." I did have a typo, i.e., the line .Range(strRow).Value=0 should have been .Range(strTemp).Value=0. When I corrected typo, it still kicks out on the line: .Range(strRow.Value = strTemp Thanks, John "Bernie Deitrick" wrote: John, You are assigning a value to a variable which you then write to a cell which is then immediately over-written with a zero: Seems like you would actually want to increment strRow, not strTemp. strTemp = "A" + CStr(nRow) .Range(strRow).Value = strTemp strTemp = "B" + CStr(nTempRow) .Range(strRow).Value = 0 HTH, Bernie MS Excel MVP "John H W" wrote in message ... I am trying to write to a cell in another sheet in the same workbook using the following code: strRow = "A" + CStr(nTempRow) With Worksheets("Temp") strTemp = "A" + CStr(nRow) .Range(strRow).Value = strTemp strTemp = "B" + CStr(nTempRow) .Range(strRow).Value = 0 End With When the code gets to fourth row, it bums out (skips to last function in module). The worksheet("Temp") is physically located as the first worksheet in the workbook. I have also used its number ("Sheet10"), but the same thing happens. The above With is within another With (have also tried this using Worksheet("Temp").Range(strRow).Value = ... In the Temp worksheet, Column A is formatted as General, Row B as integer Thanks, John H W |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow only writing in a protected Sheet | Setting up and Configuration of Excel | |||
Writing formulas between sheet | Excel Discussion (Misc queries) | |||
Writing to protected sheet from ASP.NET | Excel Programming | |||
Writing to protected sheet from ASP.NET | Excel Programming | |||
writing to excel sheet | Excel Programming |