My latest problem is that from a command in workbook1 I'm trying to open an another workbook [book2] and inserted row in a new workbook and increment the formula in the cell above by 1. eg =A24+1 to =A25+1
Can anyone help?
My latest problem is that from a command in workbook1 I'm trying to open an another workbook [book2] and inserted row in a new workbook and increment the formula in the cell above by 1. eg =A24+1 to =A25+1
Can anyone help?
Sub IncrBy1
[VBA]
Dim i as Long
Workbooks.Open Filename:= _
"C:\Test.xls"
Sheets("Sheet1").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).Select
ActiveCell.EntireRow.Insert
F = ActiveCell.Offset(-1, 0).Formula
F2 = Mid(F, 2, WorksheetFunction.Find("+", F) - 2)
ActiveCell.Formula = "=" & Left(F2, 1) & _
Val(Mid(F2, 2, Len(F2))) - 1 & "+1"
End Sub
[/VBA]
HTH//Thanks
Last edited by xls; 06-15-2013 at 08:00 AM.
Winners dont do different things, they do things differently.
I still got a problem when I run this it comes up with
"Run-Time error '1004'"
Select Method of Range class failed.
debug shows
Range("A" & Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).Select
[VBA]
Sub IncrBy1
Dim i As Long
Workbooks.Open Filename:= _
"C:\Test.xls"
'enter sheet name, change sheet1 to your sheet name
Sheets("Sheet1").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).Select
ActiveCell.EntireRow.Insert
F = ActiveCell.Offset(-1, 0).Formula
F2 = Mid(F, 2, WorksheetFunction.Find("+", F) - 2)
ActiveCell.Offset(-1, 0).Formula = "=" & Left(F2, 1) & _
Val(Mid(F2, 2, Len(F2))) - 1 & "+1"
End Sub
[/VBA]
Try above..
Check your sheet name & change sheet1.xls with your sheet name..
HTH
Winners dont do different things, they do things differently.