Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    Something like this:

    Sub FindIt()
    Dim oFoundcell As Range
    Dim vFind As Variant
    vFind = InputBox("What should I find")
    On Error Resume Next
    Set oFoundcell = ActiveSheet.Cells.Find(What:=vFind, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If oFoundcell Is Nothing Then Exit Sub
    oFoundcell.Offset(, 1).Select
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    You could use the Find method of a Range, or a combination of the Index and Match worksheet functions. Here is one example that you may be able to adapt for your own use:

    Dim rngIn As Range
    Dim rngOut As Range
    Dim lngIndex As Long
    Dim varCompare As Variant

    ' Search value
    varCompare = 3

    ' Range to be searched
    Set rngIn = ActiveSheet.Range("D18")

    ' Reference of found value
    Set rngOut = rngIn.Find(varCompare)

    If Not rngOut Is Nothing Then
    ' Offset one column to the right
    Set rngOut = rngOut.Offset(0, 1)

    ' Where did I end up?
    MsgBox rngOut.Address
    End If

    <img src=/w3timages/blueline.gif width=33% height=2>

    Here is code using Index and Match:

    Dim rngIn As Range
    Dim rngOut As Range
    Dim lngIndex As Long
    Dim varCompare As Variant

    ' Search value
    varCompare = 3

    ' Range to be searched
    Set rngIn = ActiveSheet.Range("D18")

    ' Index of found value (no error checking)
    lngIndex = Application.WorksheetFunction.Match(varCompare, rngIn, 0)

    ' Reference of found value
    Set rngOut = Application.WorksheetFunction.Index(rngIn, lngIndex, 1)

    ' Offset one column to the right
    Set rngOut = rngOut.Offset(0, 1)

    ' Where did I end up?
    MsgBox rngOut.Address

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    Sounds good, but I've a feeling it doesn't even have to be that long ...

    Both the variable and location will be found automatically; in fact there are 1828 of them which the code will loop through ...

    I'll see if I can work it out from what you gave me - many thanks!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    Wow! Thanks for that, Hans - I think I'm going to have to study it for a while to see what it does and how before I could use it!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    Jan, I'm pretty certain your suggestion is correct, but I'm still getting something wrong! I paste below the working code from my VBE; when I run it, it all works fine until the last line - I'm pretty certain I'm just phrasing the line wrong, but I can't work out for the life of me what it is!

    thisDate = Format(DateValue(Mid(rangeName, 6, 2) & "/" & Mid(rangeName, 4, 2) & "/" & dateYr), "dd/mm/yy")
    thisFigure = wBook.Names(rangeName).RefersToRange.Cells(3, 1).Value
    Workbooks("liquid.xls").Worksheets("dates").Activa te
    On Error Resume Next
    Set oFound = ActiveSheet.Range("dateRange").Find(What:=thisDate , LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    oFound.Offset(0, 1).Value = thisFigure

    Many thanks!

    PS how do I then set this to cycle through all the ranges called "day ####" in numerical order, and repeat the above for each one? Don't worry about catching errors etc, I'm sure I can work those out without using your valuable time, it's just the basic loop structure I'm stuck on ... Again, many thanks!
    Beryl M


  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    Beryl,

    The way dates are displayed is different from the way they are stored internally. Therefore the formatted date string is not found, and oFound is Nothing.

    I recommend to set thisDate to a date value, not a formatted string, and to look at the formula (assuming that the dates are constants, and not the result of a calculation). The relevant lines of code become:

    thisDate = DateSerial(dateYr, Mid(rangeName, 6, 2), Mid(rangeName, 4, 2))
    ...
    Set oFound = ActiveSheet.Range("dateRange").Find(What:=thisDate , LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)

    HTH

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    That sounds very reasonable, Hans, but oFound is still nothing. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Might it be to do with the way the dates are entered in 'dateRange'? The first (B3) is simply '=Today()', the rest are 'B3 + 1' etc ...

    Many thanks
    Beryl M


  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    Thanks, Hans, that might do it - although since I've seven different versions of this 1828-cell range to run through with this code, speed might well become an issue!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    Hi Hans, it's nearly working! oCell (I replaced the oFounds with oCells) is coming up with the right date, but it's putting the time on the end as well and therefore not matching! How do I get it to stop that? I presumed that oCell needed to be dimmed as a range ...?
    Beryl M


  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    As I indicated, my suggestion only worked if the dates were constants, not formulas.

    I hope that Jan Karel, Steve or one of the others know of a good way to use Find to search for dates as result of formulas. Instead, you could loop through the cells of dateRange, but this will be much slower:

    thisDate = DateSerial(dateYr, Mid(rangeName, 6, 2), Mid(rangeName, 4, 2))
    ...
    For Each oFound In Range("dateRange")
    If oFound = thisDate Then
    oFound.Offset(0, 1) = thisFigure
    Exit For
    End If
    Next oFound

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    Er ... well, I found a way to make it behave - I changed "if oCell = thisDate" to "if left(oCell, 8) = thisDate" and it was perfect! Is this alright, do you think, or am I going to give myself more problems doing it that way?

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Beryl M


  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    What is putting the time on the end?
    Your oCells has a date in it that is the result of a formula, right? What do you (want to) do with the found cell?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    Now I've just got the other one I was looking at (quoted from my post above) ...

    "how do I then set this to cycle through all the ranges called "day ####" in numerical order, and repeat the above for each one? Don't worry about catching errors etc, I'm sure I can work those out without using your valuable time, it's just the basic loop structure I'm stuck on ... "

    <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23> I'm really not very good with loops ...
    Beryl M


  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    Hello Beryl,

    I messed up in my previous reply, I should have used either oCell or oFound consistently. Sorry about the confusion; I have corrected it now.

    Yes, oFound or oCell should be declared As Range.

    If the first cell in dateRange has formula =TODAY(), it's strange that there should be a time part in there. But anyway, by comparing the integer values, it should be OK. Try

    If Int(oCell) = Int(thisDate) Then

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referring to cells ... (XL97/WinNT4)

    Something like

    Dim lngNum As Long
    Dim strDummy As String

    For lngNum = 1 To 9999
    rangeName = "day" & Format(lngNum, "0000")
    On Error Resume Next
    strDummy = wBook.Names(rangeName).Name
    If Err = 0 Then
    ' You only get here if the name actually exists
    On Error Goto 0 ' or On Error Goto MyErrHandler if you have error handling
    ' code to process rangeName goes here
    End If
    Next lngNum

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •