Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing value to worksheet cell
This sub causes a #Value error to reflect in the cell. It kicks out when I
am trying to write some info to a separate sheet (for verification purposes). When Excel hits the line where it write to the DebugIt sheet, Excel stops processing the calling cell and goes to the next one. As a test, I placed info into the cell (where it is suppose to write the info) and that info is placed into vTemp on that line, but I can't get it to write strTemp into that cell (the cell is not locked). The commented out lines is where I tried to use the .cells property of Worksheets instead of range - does the same thing. nRow = 221, nTempRow = 1, and nEmployed = 0 Public Sub WriteData(nRow As Integer, nTempRow As Integer, nEmployed As Integer) Dim vRow As Variant, strTemp As String, vTemp As Variant With Worksheets("DebugIt") vRow = "A" + CStr(nTempRow) ' Put col A w/nTempRow into vRow strTemp = "A" + CStr(nRow) ' Put source as "A" with row into var to write into DebugIt sheet cell ' .Cells(nTempRow, 1).Value = strTemp ' .Cells(nTempRow, 2).Value = 0 vTemp = .Range(vRow).Value .Range(vRow).Value = strTemp ' Write var into DebugIt sheet vRow = "B" + CStr(nTempRow) ' change to "B" Col vTemp = .Range(vRow).Value .Range(vRow).Value = nEmployed ' write into DebugIt sheet End With End Sub John H W |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing value to worksheet cell
If you are trying to execute this sub from a user defined function
referenced in a worksheet cell calculation, it will not work. UDFs are not permitted to change the worksheet (apart from returning a value to the calculation). It could write the value to the debug window or an external file. -- John Green Sydney Australia "John H W" wrote in message ... This sub causes a #Value error to reflect in the cell. It kicks out when I am trying to write some info to a separate sheet (for verification purposes). When Excel hits the line where it write to the DebugIt sheet, Excel stops processing the calling cell and goes to the next one. As a test, I placed info into the cell (where it is suppose to write the info) and that info is placed into vTemp on that line, but I can't get it to write strTemp into that cell (the cell is not locked). The commented out lines is where I tried to use the .cells property of Worksheets instead of range - does the same thing. nRow = 221, nTempRow = 1, and nEmployed = 0 Public Sub WriteData(nRow As Integer, nTempRow As Integer, nEmployed As Integer) Dim vRow As Variant, strTemp As String, vTemp As Variant With Worksheets("DebugIt") vRow = "A" + CStr(nTempRow) ' Put col A w/nTempRow into vRow strTemp = "A" + CStr(nRow) ' Put source as "A" with row into var to write into DebugIt sheet cell ' .Cells(nTempRow, 1).Value = strTemp ' .Cells(nTempRow, 2).Value = 0 vTemp = .Range(vRow).Value .Range(vRow).Value = strTemp ' Write var into DebugIt sheet vRow = "B" + CStr(nTempRow) ' change to "B" Col vTemp = .Range(vRow).Value .Range(vRow).Value = nEmployed ' write into DebugIt sheet End With End Sub John H W |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Writing formula for excel worksheet | New Users to Excel | |||
Writing beyond the cell specified size | Excel Discussion (Misc queries) | |||
Writing value in cell in another sheet | Excel Programming | |||
Writing to 2nd worksheet in workbook | Excel Programming | |||
writing to a cell | Excel Programming |