Concatenating Row Number from a match result and a Column Letter and storing the result as a Variable's Address

Question

I'll outline the steps I'm trying to accomplish:

1) Search through a spreadsheet for an acct # via match.

2) If it exists, I'd add offset #__ cells to the right and select that cell.

3) Set the selected cell's formula to Concatenate("ColumnLetter&Match(A1:A1000"",0) + Concatenate("ColumnLetter&Match(A1:A1000"",0)

     FX Debt             1,000       
     Fx Equity           2000        
     U.S Debt            4,000       
     U.S Loans           5,000                       

                        Recon 1 Recon 2 Diff    
     11111 $ Debt                        0  
     11112 FX Debt

So, I'd search for, say account "11111" using =match(A1:1000, "11111", 0). If it exists I'd offset to the right of it and then select that cell. I'd then add a formula to the selected cell which would add Cell references.

I'm thinking it would look something alone the lines of:

     If Match(A1:A1000,"11111",0)=true
            Select(A&(result from match))
            Offset(three to right).select

edit

So to make the next step less ambiguous I'll separate it from the rest of the code sample...First let me explain the goal with it, though. The sample data above is divided into two tables...With the first table ending, for example with the general account U.S Loans --- 5,000. The second starting with the Acct # and Recon 1. My goal is to add certain cells that contain the values (not the values themselves, I want to be able to trace back to the accounts using precedents and dependents) of the general acct's in the first table into the selected offset cell. The way I thought I'd go about this was to search for the acct name, for example "FX Debt", the same way David suggested to find the Acct #, I'd then use the similar offset method to add the cell containing 1000, so say B2, into the original offset sell to the right of the Account #.

end edit

edit 2

            Dim searchRange as Range
            Dim myMatch as Variant

                 Set searchRange = Range("A1:A1000")
                 myMatch = Match("11111", searchRange, 0)
                 If Not IsError(myMatch) Then
                      rng.Cells(myMatch).Offset(,3).Formula = Sum(Match("U.S Debt", searchRange, 0).Offset(,2)+(Match("U.S Debt", searchRange, 0).Offset(,2))...
                 End If

Does this make more sense? I'm trying to add the amounts associated with U.S Debt and U.S Loans to the master account ($ Debt).

end edit 2

"Are you matching the first account number you find and then whichever number is associated with the text string to the right of it?" First I'm looking to see if the account exists; if it does I want to take data corresponding to a table above all the account numbers and add it to a cell to the right of the account number. My data is simply two tables, one table posted onto a worksheet of an existing file. — joco, Dec 08 '16 at 20:10

score 0 · Answer 1 · edited May 23 '17 at 11:45

0

1) Search through a spreadsheet for an acct # via match.

2) If it exists, I'd add offset #__ cells to the right and select that cell.

3) Set the selected the cell's formula to Concatenate("ColumnLetter&Match(A1:A1000"",0) + Concatenate("ColumnLetter&Match(A1:A1000"",0)

Don't bother with Selecting the cell. It's unnecessary about 99% of the time (probably more). More detail, here:

How to avoid using Select in Excel VBA macros

Also, your Match syntax is wrong. You need to do:

=Match("11111", A1:A1000, 0)

So, putting it all together, something like:

Dim searchRange as Range
Dim myMatch as Variant

Set searchRange = Range("A1:A1000")
myMatch = Match("11111", searchRange, 0)
If Not IsError(myMatch) Then
    searchRange.Cells(myMatch).Offset(,3).Formula = ...
End If

I did not attempt to interpret the formula string given below; I'm not sure I understand what it's supposed to be doing:

sum(((Column Number -->)"I" + match(A1:A1000,"",0)+("I"+match(A1:A1000,"",0))

But at the very least we can consolidate your pseudo-code using the myMatch variable:

    sum(((Column Number -->)"I" + myMatch+("I"+myMatch)

(A word of caution: the + operator can be used to concatenate strings, but there are several reasons why the & operator is preferable, notably the + operator is ambiguous and defaults to a mathematical + operator when one of the arguments is a numeric type. In other words, it attempts to add a number and a string, which will invariable result in a Mismatch error)

So revise to:

    sum(((Column Number -->)"I" & myMatch & ("I"& myMatch)

Even after cleaning it up, I'm still not sure what you're trying to do with the above formula, but if you can try to explain then I can probably assist.

edited May 23 '17 at 11:45

Community

1
1

answered Dec 08 '16 at 20:17

David Zemens

53,033
11
81
130

Hey David, I edited my post to give you a better understanding of the formula – joco Dec 08 '16 at 20:58
Yeah so I still have no idea what you're trying to do or really even why you're trying to do that. I would suggest that you *make some attempt* at the code, and revise your question further with specific problem/error in your attempts. :) – David Zemens Dec 08 '16 at 21:06
Thanks for your patience! Hopefully my latest edit will do the trick – joco Dec 08 '16 at 21:17
The `Match` function returns a long/integer position, not an object, so this won't work: `Match("U.S Debt", searchRange, 0).Offset(,2)`. Possibly you intend `searchRange.Cells(Match("U.S. Debt", searchRange, 0)).Offset(,2)`? (also note this will fail if "U.S. Debt" doesn't exist in the `searchRange` – David Zemens Dec 08 '16 at 21:28
Hmm...do you know of a different pre-defined functioned that would return the cell position and add it to the sum formula and not the value of the cell? Also U.S Debt and the rest of the Account names are consistently in the dataset, so that shouldn't be a problem. – joco Dec 08 '16 at 21:42
Why do you need a different function to return the position? That's what `Match` does. – David Zemens Dec 08 '16 at 21:55
So by using match, the cell reference would be used in the formula? Also, I'm getting the generic 'Sub or Function not defined' error when trying to run the code you included in your answer, do you have any idea what the problem is? – joco Dec 08 '16 at 22:05
No, it's not the cell *reference* it's the index location of the cell within the column A1:A1000. That's why you do `searchRange.Cells(myMatch)...` (on your second follow-up, typo in my answer it should be `searchRange` not `rng`) – David Zemens Dec 08 '16 at 22:09

Concatenating Row Number from a match result and a Column Letter and storing the result as a Variable's Address

1 Answers1