Help with editing code to use OFFSET instead of cell reference

[Mark BMS]

Hi All

I was helped in my forst post on this forum with the code below but now as part of my development I want to all the routines in my project to reference their ranges fro "A1" as at a later stage I want to move the active cell and repeat the same steps.

I have hit a snag as the code below works when using a original Range as ("D5:D205") but completely fails when I ret and use OFFSET to define the range. I have stepped trough the code and it jumps out at If Not c is Nothing - which I guess means it has not seen a starting value in the range.

Sub Transpose_Data()


Dim c As Range
Dim firstaddress As String






Application.ScreenUpdating = False


'original range reference ("B5:B206") This works when added after Range in the line below
'but the current line does not


With Sheets("Data Gathering").Range("B5:B206") ' (ActiveCell.Offset(5, 1), ActiveCell.Offset(5, 205))


    Set c = .Find(What:=0, LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        firstaddress = c.Address
        Do
            TposeS1 Target:=c
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
End With
Range("A1").Activate


End Sub


Sub TposeS1(ByVal Target As Range)
        Target.Offset(0, -1).Resize(Target.Rows.Count + 1).Copy
        Target.Offset(0, 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
End Sub


Sub Fill_Time_Calc()
'Adds formula to calculate the time difference between Excpetion end and Exception Start
'If no times exist in the left side 2 cells error is replaved with ""


    With Range(ActiveCell.Offset(5, 5), ActiveCell.Offset(205, 5))
        
        .NumberFormat = "dd hh:mm"
        .Value = "=IF(SUM(RC[-2]-RC[-1])=0,"""",SUM(RC[-2]-RC[-1]))"
    
    End With
        
End Sub

Any pointers greatly appreciated. I'm on a steep learning curve here lolThanks

Mark

 

[skywriter]

The code is using range.find to look for the number 0, but it's restricted to only looking in the range B5:B206.

It is exiting at not is nothing because it didn't match that number in the restricted search range.

If it finds a match firstaddress is given the same address as the cell it found the match in.

For instance B10.

The code then looks for another match starting the search from where it found the first match.

If it finds another match it checks the address of that match against firstaddress to make sure it's not back at the match it already found.

It loops until if ends up back at the first match.

So what range do you want to search?

If it's an area that's multiple rows and columns you need to decide if you want the search to go across each row before moving down, or you want it to search down a column before moving to the next column.

Other considerations are if searching for text do you want the search to be case sensitive?

If searching for text do you want to match the whole text in the cell or is part okay?

Example: You want to match "key", but you have cells that have the word "key" and that's the only word in the cell and others have many words, "he lost the key to the lock".

If you choose whole, it will only match the cells where "key" is by itself, if you choose part, then any cell with "key" anywhere in the cell will match.

Just a couple of things to know when using range.find, there are others, but you should be aware that you have a lot of control over how you search a spreadsheet.

 

[Mark BMS]

Bruce

Thanks for the reply and a great explanation. It's really useful to understand the actual mechanics of the code and I have a far better understanding now.

On looking back at my post I realised I made a mistake in the code I posted.

I left the range in as ("B5:B205") but what I meant to post was "Range(ActiveCell.Offset(5, 1), ActiveCell.Offset(5, 205))"

With the help of you explanation I have realised I entered the argumets for both Offsets the wrong way round it should have been -

Range(ActiveCell.Offset(5, 1), ActiveCell.Offset(205, 1))

Would have taken me a while without your help.

Many thanks

 

[skywriter]

My pleasure, thanks for the feedback.