PDA

View Full Version : [SOLVED] A working macro that I need to change the search range and write location



mike scooter
01-02-2019, 01:43 PM
I can't post the format is terrible.

Kenneth Hobs
01-02-2019, 04:54 PM
Explain in words what you want to do with specific ranges detailed and expected results.

mike scooter
01-03-2019, 03:50 PM
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.

Kenneth Hobs
01-03-2019, 05:22 PM
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

mike scooter
01-04-2019, 12:22 PM
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.

Kenneth Hobs
01-04-2019, 06:07 PM
f=[PDQ1]=Range("PDQ1") it should return nothing...

mike scooter
01-05-2019, 03:32 PM
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?

Kenneth Hobs
01-05-2019, 04:19 PM
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.

mike scooter
01-07-2019, 12:42 PM
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?

Kenneth Hobs
01-07-2019, 05:10 PM
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.

mike scooter
01-08-2019, 10:25 AM
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?

Kenneth Hobs
01-08-2019, 11:03 AM
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]=

mike scooter
01-09-2019, 09:25 AM
Thanks so much for your help.