Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Lost data - need to insert formula but get error!
I'm iterating down a column of cells on Sheet1. If the cell is blank, I
want to insert the following formula: ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j & "TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & "TIRs!$C$2:$C$15000,0)))" where i and j are the column and row indexes (Dimmed as Long) for the ActiveCell. The variables do have the values. But I get the following: Application-defined or object-defined error. What I'm trying to do is pick up some data I lost from a previous version of this workbook. If the cell is blank, there may be a value that is supposed to be there on the previous version; look at Column C for that same row and, if that value matches the value in Col C on Sheet(TIRs), pull the value onto Sheet1. I use the formula in my weekly updates and it works there - *BUT* I use it without variables - it's inserted in row2 of the column and run down. I can't do that here because the older version of the data that has the lost data does not have new data I've inserted before I discovered the problem. So I'm trying to insert this modified formula into only blank cells so it picks up the ActiveCell row and column. Any help is appreciated. Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Lost data - need to insert formula but get error!
Hi Ed
maybe you have missed some comas. Try "=IF(ISERROR(MATCH($C" & j &",TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & ",TIRs!$C$2:$C$15000,0)))" -- Regards Frank Kabel Frankfurt, Germany Ed wrote: I'm iterating down a column of cells on Sheet1. If the cell is blank, I want to insert the following formula: ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j & "TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & "TIRs!$C$2:$C$15000,0)))" where i and j are the column and row indexes (Dimmed as Long) for the ActiveCell. The variables do have the values. But I get the following: Application-defined or object-defined error. What I'm trying to do is pick up some data I lost from a previous version of this workbook. If the cell is blank, there may be a value that is supposed to be there on the previous version; look at Column C for that same row and, if that value matches the value in Col C on Sheet(TIRs), pull the value onto Sheet1. I use the formula in my weekly updates and it works there - *BUT* I use it without variables - it's inserted in row2 of the column and run down. I can't do that here because the older version of the data that has the lost data does not have new data I've inserted before I discovered the problem. So I'm trying to insert this modified formula into only blank cells so it picks up the ActiveCell row and column. Any help is appreciated. Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Lost data - need to insert formula but get error!
Thanks, Frank. I did miss those. But it didn't help. I still get the
error. Is there maybe a better way to deal with the variables? Ed "Frank Kabel" wrote in message ... Hi Ed maybe you have missed some comas. Try "=IF(ISERROR(MATCH($C" & j &",TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & ",TIRs!$C$2:$C$15000,0)))" -- Regards Frank Kabel Frankfurt, Germany Ed wrote: I'm iterating down a column of cells on Sheet1. If the cell is blank, I want to insert the following formula: ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j & "TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & "TIRs!$C$2:$C$15000,0)))" where i and j are the column and row indexes (Dimmed as Long) for the ActiveCell. The variables do have the values. But I get the following: Application-defined or object-defined error. What I'm trying to do is pick up some data I lost from a previous version of this workbook. If the cell is blank, there may be a value that is supposed to be there on the previous version; look at Column C for that same row and, if that value matches the value in Col C on Sheet(TIRs), pull the value onto Sheet1. I use the formula in my weekly updates and it works there - *BUT* I use it without variables - it's inserted in row2 of the column and run down. I can't do that here because the older version of the data that has the lost data does not have new data I've inserted before I discovered the problem. So I'm trying to insert this modified formula into only blank cells so it picks up the ActiveCell row and column. Any help is appreciated. Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Lost data - need to insert formula but get error!
Hi
after inserting these two comas the formula is inserted for me (replacing your variables with some dummy values) -- Regards Frank Kabel Frankfurt, Germany Ed wrote: Thanks, Frank. I did miss those. But it didn't help. I still get the error. Is there maybe a better way to deal with the variables? Ed "Frank Kabel" wrote in message ... Hi Ed maybe you have missed some comas. Try "=IF(ISERROR(MATCH($C" & j &",TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & ",TIRs!$C$2:$C$15000,0)))" -- Regards Frank Kabel Frankfurt, Germany Ed wrote: I'm iterating down a column of cells on Sheet1. If the cell is blank, I want to insert the following formula: ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j & "TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & "TIRs!$C$2:$C$15000,0)))" where i and j are the column and row indexes (Dimmed as Long) for the ActiveCell. The variables do have the values. But I get the following: Application-defined or object-defined error. What I'm trying to do is pick up some data I lost from a previous version of this workbook. If the cell is blank, there may be a value that is supposed to be there on the previous version; look at Column C for that same row and, if that value matches the value in Col C on Sheet(TIRs), pull the value onto Sheet1. I use the formula in my weekly updates and it works there - *BUT* I use it without variables - it's inserted in row2 of the column and run down. I can't do that here because the older version of the data that has the lost data does not have new data I've inserted before I discovered the problem. So I'm trying to insert this modified formula into only blank cells so it picks up the ActiveCell row and column. Any help is appreciated. Ed |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Lost data - need to insert formula but get error!
Well, like I said:
"Frank Kabel" wrote in message ... Hi after inserting these two comas the formula is inserted for me (replacing your variables with some dummy values) <<snip I use the formula in my weekly updates and it works there - *BUT* I use it without variables - it's inserted in row2 of the column and run down. I can't do that here because the older version of the data that has the lost data does not have new data I've inserted before I discovered the problem. So I'm trying to insert this modified formula into only blank cells so it picks up the ActiveCell row and column. I need the variables to pick up the ActiveCell row and column indexes and put them into the formula. Ed |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Lost data - need to insert formula but get error!
Hi Ed
then please your complete code (otherwise it's complicated to identify your error) -- Regards Frank Kabel Frankfurt, Germany Ed wrote: Well, like I said: "Frank Kabel" wrote in message ... Hi after inserting these two comas the formula is inserted for me (replacing your variables with some dummy values) <<snip I use the formula in my weekly updates and it works there - *BUT* I use it without variables - it's inserted in row2 of the column and run down. I can't do that here because the older version of the data that has the lost data does not have new data I've inserted before I discovered the problem. So I'm trying to insert this modified formula into only blank cells so it picks up the ActiveCell row and column. I need the variables to pick up the ActiveCell row and column indexes and put them into the formula. Ed |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Lost data - need to insert formula but get error!
"Frank Kabel" wrote in message
... Hi Ed then please your complete code (otherwise it's complicated to identify your error) Here it is. The loop is copied six times total - I got tired of trying to select the next range based on the variables (couldn't get that either!), and decided a chainsaw was as good as a scalpel if it got the job done! But it doesn't get past the first time without an error. And the other five times will be exactly the same except for the starting cell. Thanks for all your help. I know you actually have a real life out there somewhere, and I appreciate the time you're taking on this. Ed Sub Recover_Data() Dim strLink As String Dim rngLast As String Dim i As Long ' Column Dim j As Long ' Row ' Delete Zero values in affected columns Range("V:W").Select Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True ' Last cell rngLast = Range("B65536").End(xlUp).Row ' First Column ' First cell Range("V2").Select ' Loop through cells Do While ActiveCell.Row < rngLast i = ActiveCell.Column j = ActiveCell.Row ' Get value strLink = ActiveCell.Value ' If there's nothing in the cell If strLink = "" Then 'ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j & "TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & "TIRs!$C$2:$C$15000,0)))" ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j & ",TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & ",TIRs!$C$2:$C$15000,0)))" End If ' Move down ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Loop ' Loop repeats five more times End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Lost data - need to insert formula but get error!
Hi
problem was you were using the column index (22) as variable (Excel expected 'V'). I changed your code: - no need for selects / activate statements - use the formula r1C1 notation instead Sub Recover_Data() Dim strLink As String Dim rngLast As Long Dim row_index As Long ' Delete Zero values in affected columns Range("V:W").Select Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True ' Last cell rngLast = Range("B65536").End(xlUp).Row For row_index = 2 To rngLast With Cells(row_index, "V") If .Value = "" Then .FormulaR1C1 = "=IF(ISERROR(MATCH(R[0]C3,TIRs!R2C3:R15000C3,0)),0,INDEX(R2C[0]:R15000C [0],MATCH(R[0]C3,TIRs!R2C3:R15000C3,0)))" End If End With Next row_index End Sub -- Regards Frank Kabel Frankfurt, Germany Ed wrote: "Frank Kabel" wrote in message ... Hi Ed then please your complete code (otherwise it's complicated to identify your error) Here it is. The loop is copied six times total - I got tired of trying to select the next range based on the variables (couldn't get that either!), and decided a chainsaw was as good as a scalpel if it got the job done! But it doesn't get past the first time without an error. And the other five times will be exactly the same except for the starting cell. Thanks for all your help. I know you actually have a real life out there somewhere, and I appreciate the time you're taking on this. Ed Sub Recover_Data() Dim strLink As String Dim rngLast As String Dim i As Long ' Column Dim j As Long ' Row ' Delete Zero values in affected columns Range("V:W").Select Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True ' Last cell rngLast = Range("B65536").End(xlUp).Row ' First Column ' First cell Range("V2").Select ' Loop through cells Do While ActiveCell.Row < rngLast i = ActiveCell.Column j = ActiveCell.Row ' Get value strLink = ActiveCell.Value ' If there's nothing in the cell If strLink = "" Then 'ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j & "TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & "TIRs!$C$2:$C$15000,0)))" ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j & ",TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & ",TIRs!$C$2:$C$15000,0)))" End If ' Move down ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Loop ' Loop repeats five more times End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Lost data - need to insert formula but get error!
Frank, thank you so much! I have no idea what time it is in Germany, but I
know you've spent some on this with me, and I am very grateful. I will study through what you've given me so I can understand it myself, rather than just copy from nice people! 8) One Question: can I assume in "R[0]C3" and "R2C[0]", the [0] is automatically picking up the index number from the active row and column? Ed "Frank Kabel" wrote in message ... Hi problem was you were using the column index (22) as variable (Excel expected 'V'). I changed your code: - no need for selects / activate statements - use the formula r1C1 notation instead Sub Recover_Data() Dim strLink As String Dim rngLast As Long Dim row_index As Long ' Delete Zero values in affected columns Range("V:W").Select Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True ' Last cell rngLast = Range("B65536").End(xlUp).Row For row_index = 2 To rngLast With Cells(row_index, "V") If .Value = "" Then .FormulaR1C1 = "=IF(ISERROR(MATCH(R[0]C3,TIRs!R2C3:R15000C3,0)),0,INDEX(R2C[0]:R15000C [0],MATCH(R[0]C3,TIRs!R2C3:R15000C3,0)))" End If End With Next row_index End Sub -- Regards Frank Kabel Frankfurt, Germany Ed wrote: "Frank Kabel" wrote in message ... Hi Ed then please your complete code (otherwise it's complicated to identify your error) Here it is. The loop is copied six times total - I got tired of trying to select the next range based on the variables (couldn't get that either!), and decided a chainsaw was as good as a scalpel if it got the job done! But it doesn't get past the first time without an error. And the other five times will be exactly the same except for the starting cell. Thanks for all your help. I know you actually have a real life out there somewhere, and I appreciate the time you're taking on this. Ed Sub Recover_Data() Dim strLink As String Dim rngLast As String Dim i As Long ' Column Dim j As Long ' Row ' Delete Zero values in affected columns Range("V:W").Select Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True ' Last cell rngLast = Range("B65536").End(xlUp).Row ' First Column ' First cell Range("V2").Select ' Loop through cells Do While ActiveCell.Row < rngLast i = ActiveCell.Column j = ActiveCell.Row ' Get value strLink = ActiveCell.Value ' If there's nothing in the cell If strLink = "" Then 'ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j & "TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & "TIRs!$C$2:$C$15000,0)))" ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j & ",TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & ",TIRs!$C$2:$C$15000,0)))" End If ' Move down ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Loop ' Loop repeats five more times End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP! Lost data - need to insert formula but get error!
Hi
your assumption regarding R[0] is correct. for example R[-1]C[1] would pick the cell one row above and one column to the right. More information about this can be found in the Excel help under R1C1 reference -- Regards Frank Kabel Frankfurt, Germany Ed wrote: Frank, thank you so much! I have no idea what time it is in Germany, but I know you've spent some on this with me, and I am very grateful. I will study through what you've given me so I can understand it myself, rather than just copy from nice people! 8) One Question: can I assume in "R[0]C3" and "R2C[0]", the [0] is automatically picking up the index number from the active row and column? Ed "Frank Kabel" wrote in message ... Hi problem was you were using the column index (22) as variable (Excel expected 'V'). I changed your code: - no need for selects / activate statements - use the formula r1C1 notation instead Sub Recover_Data() Dim strLink As String Dim rngLast As Long Dim row_index As Long ' Delete Zero values in affected columns Range("V:W").Select Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True ' Last cell rngLast = Range("B65536").End(xlUp).Row For row_index = 2 To rngLast With Cells(row_index, "V") If .Value = "" Then .FormulaR1C1 = "=IF(ISERROR(MATCH(R[0]C3,TIRs!R2C3:R15000C3,0)),0,INDEX(R2C[0]:R15000C [0],MATCH(R[0]C3,TIRs!R2C3:R15000C3,0)))" End If End With Next row_index End Sub -- Regards Frank Kabel Frankfurt, Germany Ed wrote: "Frank Kabel" wrote in message ... Hi Ed then please your complete code (otherwise it's complicated to identify your error) Here it is. The loop is copied six times total - I got tired of trying to select the next range based on the variables (couldn't get that either!), and decided a chainsaw was as good as a scalpel if it got the job done! But it doesn't get past the first time without an error. And the other five times will be exactly the same except for the starting cell. Thanks for all your help. I know you actually have a real life out there somewhere, and I appreciate the time you're taking on this. Ed Sub Recover_Data() Dim strLink As String Dim rngLast As String Dim i As Long ' Column Dim j As Long ' Row ' Delete Zero values in affected columns Range("V:W").Select Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True ' Last cell rngLast = Range("B65536").End(xlUp).Row ' First Column ' First cell Range("V2").Select ' Loop through cells Do While ActiveCell.Row < rngLast i = ActiveCell.Column j = ActiveCell.Row ' Get value strLink = ActiveCell.Value ' If there's nothing in the cell If strLink = "" Then 'ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j & "TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & "TIRs!$C$2:$C$15000,0)))" ActiveCell.Formula = "=IF(ISERROR(MATCH($C" & j & ",TIRs!$C$2:$C$15000,0)),0,INDEX(TIRs!" & i & "$2:" & i & "$15000,MATCH($C" & j & ",TIRs!$C$2:$C$15000,0)))" End If ' Move down ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate Loop ' Loop repeats five more times End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File Error: Data may have been lost. | Excel Discussion (Misc queries) | |||
File Error:Data may be lost | Excel Discussion (Misc queries) | |||
File Error: Data May Have Been Lost | Excel Discussion (Misc queries) | |||
File Error: Data May Have Been Lost | Setting up and Configuration of Excel | |||
File error: Data may have been lost. | Excel Programming |