Consulting

Results 1 to 13 of 13

Thread: A working macro that I need to change the search range and write location

  1. #1

    A working macro that I need to change the search range and write location

    I can't post the format is terrible.
    Attached Files Attached Files
    Last edited by mike scooter; 01-02-2019 at 01:48 PM. Reason: a mess

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Explain in words what you want to do with specific ranges detailed and expected results.

  3. #3
    Hi Thanks for your reply. I've written it out better. I was wondering if I could get help with writing a macro to find numbers 1 to 12 and write the cell location. The cell range is as follows: B5:B16, F5:F16, J5:J16. Search for numbers 1 to 12. A positive result only occurs when the 3 cells to the right of a found number also has numbers in it. Please do not hard code I will have to make changes. This is only a sample Excel sheet. Write the positive number cell location to the corresponding number in the following cells: Number 1 write to cell location B2 Number 2 write to cell location C2 Number 3 write to cell location D2 Number 4 write to cell location E2 Number 5 write to cell location F2 Number 6 write to cell location G2 Number 7 write to cell location H2 Number 8 write to cell location I2 Number 9 write to cell location J2 Number 10 write to cell location K2 Number 11 write to cell location L2 Number 12 write to cell location M2 So as an example, in my Excel spreadsheet, the code would find the 4 in cell B12 and would write B12 to cell E2 and so on. Thanks so much for your help.
    Attached Files Attached Files

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    This is a bit more work since you formatted numbers as text.

    Put code into a Module. Use as a Sub or as a UDF.

    Sub Test_f3R() 
      'MsgBox Nums3R([c11])
      MsgBox f3R([E1], Range("B5:B16,F5:F16,J5:J16"))
      MsgBox f3R([F1], Range("B5:B16,F5:F16,J5:J16"))
      MsgBox f3R([J1], Range("B5:B16,F5:F16,J5:J16"))
      MsgBox f3R([PDQ1], Range("B5:B16,F5:F16,J5:J16")) 'Nothing...
    End Sub
    
    
    'f is range address of number to find
    'r is range to look for f's value with 3 numbers to right.
    '=f3r(B1,($B$5:$B$16,$F$5:$F$16,$J$5:$J$16))
    Function f3R(f As Range, r As Range) As String
      Dim c As Range
      For Each c In r
        If f = c Then
          If Nums3R(c) Then
            f3R = c.Address(False, False)
            Exit Function
          End If
        End If
      Next
    End Function
    
    
    Function IsNumber(c As Range) As Boolean
      IsNumber = c + 1 <> 1
    End Function
    
    
    Function Nums3R(c As Range) As Boolean
      Nums3R = IsNumber(c.Offset(, 1)) And IsNumber(c.Offset(, 2)) And IsNumber(c.Offset(, 3))
    End Function

  5. #5
    Hi Kenneth Thank-you so much for your reply/code. The code looks really impressive but one issue is that the code gets highlighted with this line, " MsgBox f3R([PDQ1], Range("B5:B16,F5:F16,J5:J16")) 'Nothing...". Can I ask you what the PDQ1 refers to? It may help solve the issue. Thanks again, Mike.

  6. #6
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    f=[PDQ1]=Range("PDQ1") it should return nothing...

  7. #7
    Hi Kenneth Sorry to bother you again. I tried your suggestion with no luck. VBA changes the code automatically to: f = [PDQ1] = Range("PDQ1") but did not work. I have tried the following lines but did not work: MsgBox f = [PDQ1] = Range("PDQ1") MsgBox f = [PDQ1] = Range("PDQ1"), Range("B5:B16,F5:F16,J5:J16")) Is it something I知 doing/not doing?

  8. #8
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    You lost me. #6 explained what you asked in #5.

    PDQ1 is just a cell range. It was a test input for the function f3r()'s 1st input parameter.

    Decide if you want to run it as a Sub to do the work or use the function as a UDF as shown and explained in #4.

    Look at the comment above the function. It shows what formula to put into B2 of your example file. Then drag and fill right. In the attachment, I put the UDF into B3 and filled right to show how it matched your manual example in row 2.
    Attached Files Attached Files

  9. #9
    Hi Kenneth I think I know what the problem is:I知 not putting the code in a cell. When you said put it in a module I put it in module on the left where I always put VBA code. I知 running a number of other vba code in the same module depending on what information I知 after. Is there a way to adjust the code or do I have this all wrong?

  10. #10
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    From #4:
    Use as a Sub or as a UDF.
    The Test Sub showed 4 calls to the custom function. The comment above the function showed how to use it as a UDF in a cell.

    Other than some special things like Option, API functions and constants, all public code can go into one Module. There is nothing wrong with more than one Module though. In my Personal workbook, I keep string routines in one module, API routines in one, FSO in one, arrays into one, etc.

    How you change it is up to you. e.g. It could easily be used as an array UDF.

  11. #11
    Hi Kenneth Sorry to bother you again. I just ran the code in the Excel sheet you posted with your code and two things: code isn稚 writing the cell location anywhere and it痴 having the same problem with the line MsgBox f3R([PDQ1], Range("B5:B16,F5:F16,J5:J16")) that I知 having on my Excel workbook. Not sure what to do?

  12. #12
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    The UDF in row 3 below your row1 returns the cell string, just as your manual entry shows on row 2.

    If you want values assigned in the Sub instead or in addition to, change msgbox to the cell. E.g. [A1]=

  13. #13
    Thanks so much for your help.
    Last edited by mike scooter; 01-09-2019 at 09:26 AM. Reason: grammer

Posting Permissions

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