Hi everyone!
I am having trouble figuring out how to use the method .Find... What I am trying to do is search column("A:A") for the first instance of a string contained in a variable. If found then insert a row above it pushing everything down) and populate that row with Data (Value in column B tells you the version... so version that got pushed down +1). If it doesn't find anything then insert a row at 2 (pushing everything else down) an populate it.
I have managed so far:
Sub Eckdatenblatt()
Application.ScreenUpdating = False
Dim FindRow As Range
Dim St_Project As String
Set EW_ECK = Workbooks.Open("S:\Eckdatenblatt\Eckdatenblatt.xlsx")
Set Summary = EW_ECK.Sheets("Summary")
Summary.Activate
With Summary
Columns("A:A").Select
Selection.Find(What:=St_Project, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[1]C+1"
ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
EW_ECK.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
The problem is that the rows get shifted down and then work is being carried out below the data instead of on top...
In the example attached: If user searched for Project "Other Project", then shift everything down from row 5 and add one to the version
It also crashes if the string is not found and I am not sure how to handle the error and start at the top (row 2) with a fresh line and version 1.
The code gets executed from a different file where all the data is contained.
Many Thanks!
Bookmarks