It worked once and found the forth valid cell in the range, ever since then it starts and then excel stops responding.
I'm guessing there is no end to the loop but not sure,can someone tell me whats up with this code please?
Sub foo()
Dim UserId As String, CaseId As String, SearchString As String
Dim DocGrid As Worksheet, S2LI As Worksheet
Dim CellRef(10) As Range
Dim RowNumba As Long, i As Long, x As Long
Application.ScreenUpdating = False
Set DocGrid = ThisWorkbook.Sheets(1)
Set S2LI = ThisWorkbook.Sheets(2) ' Set the worksheet, Change to suit
UserId = DocGrid.Range("Q3").Value
CaseId = DocGrid.Range("R3").Value
SearchString = UserId & CaseId
If DocGrid.Range("R3") = "" Then
MsgBox "Complete Case Reference"
End If
' Change the (2) below to the column number you're searching (2 = B:B)
RowNumba = S2LI.Columns(2).Find(What:=SearchString, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
For x = 0 To 10
Set CellRef(x) = S2LI.Cells(RowNumba, 53 + (x * 4))
Next x
'Pastes Template Letters Into Their Relevant Column in "Database" Tab
For x = 0 To 10
i = 12
Do Until IsEmpty(CellRef(x).Value)
With DocGrid
ApplyMyFormat (.Cells(i, 7).Resize(1, 3))
ApplyMyFormat (.Cells(i, 10).Resize(1, 3))
ApplyMyFormat (.Cells(i, 13).Resize(1, 3))
ApplyMyFormat (.Cells(i, 16).Resize(1, 4))
.Cells(i, 16).Resize(1, 4) = CellRef(x).Value
.Cells(i, 7).Resize(1, 3) = CellRef(x).Offset(0, 1).Value
.Cells(i, 10).Resize(1, 3) = CellRef(x).Offset(0, 3).Value
.Cells(i, 13).Resize(1, 3) = CellRef(x).Offset(0, 2).Value
End With
i = i + 1
Loop
Next x
Application.ScreenUpdating = True
MsgBox "Done Muvva Funka!!!"
End Sub
Sub ApplyMyFormat(myRange As Range)
With myRange
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
.WrapText = True
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
End Sub
Bookmarks