Help with VBA code

[Frank3923]

I have the following code on the internet, to search for a string and return the row number. The enclosed code, does that. Being that I am not a programmer,
I would like to know, what I would need to do, if the value and the row number returned, does not meet a certain value, then insert rows.

example- I am searching for the string "Field Number". It returns a value of row No. 42, I need Field number to be in row 45, so I would need to insert 3 rows, above the found string value of "Field Number".

example~2, If found string value is in row 42, insert 3 rows.

If string value, and findrow=45, Do Nothing.

Sub xFind_String2() '(sFindText As String)
Dim i As Integer ' Integer used in 'For' loop
Dim iRowNumber As Integer ' Integer to store result in

iRowNumber = 0

' Loop through cells c1-c100 until 'sFindText' is found

For i = 1 To 100
If Cells(i, 3).Value = "Field Number" Then

' A match has been found to the supplied string
' Store the current row number and exit the 'For' Loop
iRowNumber = i
Exit For

End If
Next i

' Pop up a message box to let the user know if the text
' string has been found, and if so, which row it appears on

If iRowNumber = 0 Then
MsgBox "String " & sFindText & " not found"
Else
MsgBox "String " & sFindText & " found in cell C" & iRowNumber
End If

End Sub

Thank you in advance, for any suggestions in solving the code that would be needed.

 

[Warship]

    If iRowNumber < 45 Then
        For i = 1 To 45 - iRowNumber
            Cells(i, 3).Insert Shift:=xlDown 'insert rows in Col C
            'Rows(iRowNumber).Insert Shift:=xlDown 'insert entire rows
        Next i
    End If

What if iRowNumber is >45?

 

[Frank3923]

Thank you for the response.
I have tried implementing the code that you provided; the results were the contents of Column “C” moving down 45 rows. To make myself clear, the contents of cell “C1” in now in “C46”.
So I obviously, do not have the code in the proper sequence. Any further suggestions would be appreciated. I have put the code below.

Sub xFind_String2x1a() '(sFindText As String)
Dim i As Integer ' Integer used in 'For' loop
Dim iRowNumber As Integer ' Integer to store result in

iRowNumber = 0

' Loop through cells c1-c100 until 'sFindText' is found

For i = 1 To 100
If Cells(i, 3).Value = "Field Number" Then

' A match has been found to the supplied string
' Store the current row number and exit the 'For' Loop
iRowNumber = i
Exit For

End If
Next i

' Pop up a message box to let the user know if the text
' string has been found, and if so, which row it appears on

'If iRowNumber = 0 Then
'MsgBox "String " & sFindText & " not found"
'Else
'MsgBox "String " & sFindText & " found in cell C" & iRowNumber
'End If
'.....
If iRowNumber < 45 Then
For i = 1 To 45 - iRowNumber
Cells(i, 3).Insert Shift:=xlDown 'insert rows in Col C
'Rows(iRowNumber).Insert Shift:=xlDown 'insert entire rows
Next i
End If
End Sub

 

[Rick Rothstein]

This would be a better approach to do what your posted code does (which is not what your write up says you want to do)...

Sub xFind_String2(sFindText As String)
  Dim FoundCell As Range
  Const SearchRange As String = "C1:C100"
 
  '  Search the range specified in the SearchRange constant for the passed in "text to find"
  Set FoundCell = Range(SearchRange).Find(sFindText, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
 
  '  Test if the passed in "text to find" was actually found
  If FoundCell Is Nothing Then
    MsgBox "String " & sFindText & " not found"
  Else
    MsgBox "String " & sFindText & " found in cell " & FoundCell.Address(0, 0)
  End If
End Sub

 

[Frank3923]

Your code finds the text as requested, what further steps would I need to take, to instruct the macro that if ("FoundCell.Address") does not equal row 45, then insert the number of rows needed, across all columns in the worksheet, so that the "text to find", would then be in row 45 after doing the row insert.

Thank you in advance

 

[Rick Rothstein]

Your code finds the text as requested, what further steps would I need to take, to instruct the macro that if ("FoundCell.Address") does not equal row 45, then insert the number of rows needed, across all columns in the worksheet, so that the "text to find", would then be in row 45 after doing the row insert.

Before I can answer that, I need to know what should happen if the text was found in a row greater than 45? While I am asking... did you really want the MsgBoxes in your final code or not (the answer affects where the code goes and how the If..Then should be written)?

 

[Frank3923]

Unless there was a major change in the manner, that I receive the data, it should never go past row 45.

In answer to your question regarding the message boxes, I do not need them.

Thank you, for all your help.

 

[Rick Rothstein]

See if this subroutine does what you want...

Sub xFind_String2(sFindText As String)
  Dim FoundCell As Range
  Const SearchRange As String = "C1:C100"
 
  '  Search the range specified in the SearchRange constant for the passed in "text to find"
  Set FoundCell = Range(SearchRange).Find(sFindText, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
 
  '  Test if the passed in "text to find" was actually found
  If Not FoundCell Is Nothing Then
    '  Check if the found text is located before Row 45
    If FoundCell.Row < 45 Then
      '  insert the correct number of rows in front of the row with the found text on it
      FoundCell.Resize(45 - FoundCell.Row).EntireRow.Insert
    End If
  End If
End Sub

 

[Frank3923]

It does exactly, what I needed.

Thank you for all your time and Knowledge.

Frank