Finding values across a large range

[Subbie]

Hi
With the help of this thread I created a complicated workbook for a charity to record daily transactions.
They have asked me to add a sheet that has the following:

The data runs from Column V to Colum FU and rows 4 to 15. The pattern is regular across the range:
Week Rec Amount
The VBA routine enters ‘Yes’ in Rec Column.
Every week a routine has to be run and I need the Range searched to find the blank cells that have a value to the left column and the right column. These adjacent values have to be posted in another range on the sheet:

So far I have tried using vba code to find blanks and copy adjacent cells, but I could not get a code to work.
I then tried Excel, VLOOKUP, MATCH and AGGREGATE. The best I got was copying the first cell W4 and the formula stopped.
Could anyone here point me in the right direction?
Would be very grateful.
Subbie

 

[kweaver]

Your images didn't show up.

 

[Subbie]

Your images didn't show up.

Hmm..Not sure why. I can see them in my Chrome browser...Not sure what to do.

 

[Subbie]

Hmm..Not sure why. I can see them in my Chrome browser...Not sure what to do.

Just tried Firefox they also appear there.

 

[Subbie]

Have now worked out a solution that works...to a point:
<code> 'End Sub

Public Function CopyRecNo()
On Error GoTo error:
' select the range of weeks and search for null rec yes entries
' cells to paste data into O4:P4 to O26 to P26 (4,15) is the first
' cell range to copy from w4 and then every 3 cells
' cell -1 is cheque and +1 is amount
' read in range
' sort null rec
' create list and paste into cells
' 11 rows down
' FU14 end of data
' starting colum is 22


' Turn off screen refresh
Application.ScreenUpdating = True




' Dim as arrays


Dim RecRow As Integer
Dim RecCol As Integer
Dim Recchq(146) As String
Dim Recamm(146) As Long
Dim RecRec(146) As String
Dim DataCount As Integer
Dim loopcount As Integer




' Set starting point


RecRow = 4
RecCol = 22
DataCount = 0




' Set starting point of search
Sheets("BankRec").Activate
Cells(RecRow, RecCol).Select




' Set up array loop
For loopcount = 0 To 58 Step 1




RecCol = RecCol + 3
Cells(RecRow, RecCol).Select
Recchq(DataCount) = ActiveCell.Value
RecRec(DataCount) = ActiveCell.Offset(0, 1).Value
Recamm(DataCount) = ActiveCell.Offset(0, 2).Value
DataCount = DataCount + 1


Next loopcount


' Start writing the data to the two columns


DataCount = 1
RecRow = 4
RecCol = 15

For loopcount1 = 0 To 138 Step 1
Cells(RecRow, RecCol).Select
ActiveCell.Value = Recchq(DataCount)
ActiveCell.Offset(0, 1).Value = Recamm(DataCount)
DataCount = DataCount + 1
RecRow = RecRow + 1
Next loopcount1



Debug.Print , Recchq(DataCount), RecRec(DataCount), Recamm(DataCount), DataCount, RecRow




error:
Debug.Print Err.Number, Err.Description, Err.Source

End Function
</code>
The only problem is that the result is:

How do I remove lines that do not have values. Have tried IsEmpty and = "" etc. But hey fail and either show nothing or the same result. Appreciate any help to resolve.

 

[Subbie]

Please ignore this - have now resolved it myself.