Solved

Excel: Identify which column has the desired value

Posted on 2013-01-29
53
302 Views
Last Modified: 2013-04-10
I have a list of values in one sheet. On another sheet, I have multiple values in various different columns. Each column has unique values, so there is no chance that a value is on two different columns. What I want is some formula (macro not desired, only if unavoidable) that will identify which column it comes from. I know I can achieve this using chained IFs and VLookups, but those seem inefficient and only work for a set number of columns.

=IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,0)),IF(ISNA(VLOOKUP(A1,Sheet2!B:B,1,0)),"","Col2"),"Col1")

Using this, if I want to add new columns, I have to add another chained IF. Also, it would seem that this would become very slow, very soon. The first list has about 30k rows. Each column on the second sheet will have something between 1k and 7k.

So, what is the best solution for this? Notice that the names Col1/Col2 aren't mandatory, though some way for me to customize the output is desirable.
0
Comment
Question by:Cluskitt
  • 27
  • 14
  • 5
  • +2
53 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 38831674
Try:



=SUMPRODUCT((Sheet2!$A$1:$Z$100=A1)*((COLUMN(Sheet2!$A$1:$Z$100)-COLUMN(Sheet2!$A$1)+1)))

This will give the column number within range A1:Z100.

Adjust as necessary.  It is recommended not to use whole column references with this formula, due to inefficiency.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38831716
I don't know how that can be made to work with my problem. What I want is:
Sheet1 Cell A1 has value XPTO. I want cell B to tell me which column in sheet2 has the value XPTO.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38831728
Try it.

this looks in all cells A1:Z100, and returns TRUE for where found, then it multiplies by array of column numbers to give the actual column with the word in  it.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38831814
Sorry, I hadn't noticed the =A1
This works fine, but it is waaay too slow.I have to extend to row 10k, and it just hung my excel. The range I used was A1:L10000
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38831840
Pehaps you can add a row in Sheet2, below row 10000 with formula (assuming row 10001)

=Isnumber(match(Sheet1!$A$1,A1:A10000,0))

copied across.

then in B1 of Sheet1.

=MATCH(TRUE,Sheet2!A10001:L10001,0)  to give column number.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38831984
Wouldn't that just match Sheet1!A1? It wouldn't match the whole column.

I think it would be easier with a practical example, which is what I should have started doing in the first place. I have attached a sample file. There are only 20 values, but it should be enough so you understand what's expected. Column A on sheet1 has something over 30k values. There are only about 15 on sheet2, but that isn't a problem. I've only included so many results so you can test performance issues.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38832087
No attachment
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38834292
Oops... sorry :P
Sample.xlsx
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38834957
I think the most efficient method in this case is probably a nested IF() with COUNTIF functions checking each column.  With nested IF statements, as soon as one is found to be TRUE, starting from left most check, the function stops and doesn't calculate the rest.... so some cells will be a bit slower to calculate if the values end up being in the latter columns, but overall it shouldn't be that slow.

Test this formula:

=IF(COUNTIF(Folha2!A:A,A2),1,IF(COUNTIF(Folha2!B:B,A2),2,IF(COUNTIF(Folha2!C:C,A2),3,IF(COUNTIF(Folha2!D:D,A2),4,IF(COUNTIF(Folha2!E:E,A2),5,IF(COUNTIF(Folha2!F:F,A2),6,IF(COUNTIF(Folha2!G:G,A2),7,IF(COUNTIF(Folha2!H:H,A2),8,IF(COUNTIF(Folha2!I:I,A2),9,IF(COUNTIF(Folha2!J:J,A2),10,IF(COUNTIF(Folha2!K:K,A2),11,IF(COUNTIF(Folha2!L:L,A2),12,IF(COUNTIF(Folha2!M:M,A2),13,IF(COUNTIF(Folha2!N:N,A2),14,IF(COUNTIF(Folha2!O:O,A2),15,"")))))))))))))))
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38835059
Try out how this macro performs compared with the formula

Sub lookup()
    nv = WorksheetFunction.CountA(Sheet2.Range("A:F"))
    Set target = Sheet1.Range("A1:A" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row)
    For Each cel In Sheet2.Range("A:F")
        If cel <> "" Then
            Set fnd = target.Find(cel, , , xlWhole)
            n = n + 1
            If n Mod 100 = 0 Then Application.StatusBar = n & " of " & nv
            If Not fnd Is Nothing Then fnd.Offset(, 1) = cel.Column
        End If
    Next cel
    Application.StatusBar = ""
End Sub
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38835749
The chained countifs will hang excel to a point where I have to kill the excel process. After a few minutes I had glimpsed excel calculating (was at 58%). I used a parcial range for each column (1 to 10000).
Don't know how countifs will fare against vlookups, but that was pretty much my initial solution already, which, as I said, is inefficient due to the large numbers involved.

The code doesn't quite seem to work. I have failed to grasp some of what you intended so I can't adapt it (I'm fairly adept at VBA). Problems I found:
1- You're using Sheet2.Range, which doesn't work. I changed it to Sheets(2).Range
2- After some time, I stopped the code. None were flagged (though the first one should be) and n was somewhere near 15k.
3- Your code seems to be looping sheet2 and searching sheet1. There is a problem with that, which is that there are repeated values on sheet1, all of which need to be tagged. It's only sheet2 which has unique values.

That did inspire me to create a macro of my own. In the real example I'm using column J as the one to be matched and column B as the one to flag. Otherwise it's the same. Also, the real example has 6933 rows in column A and 194 on the second (just so I'd have some values on another column. I expect to fill one column per week). Sheet1 has 33797 rows (this value is fixed).

This is the code I used:
Sub FillCol()
    Application.ScreenUpdating = False
    Dim target As Range
    For Each cel In Sheets(1).Range("J1:J" & Range("J" & Sheets(1).Rows.Count).End(xlUp).Row)
        For a = 1 To 12
            Set target = Sheets(2).Range(Chr(64 + a) & "1:" & Chr(64 + a) & Sheets(2).Range(Chr(64 + a) & Sheets(2).Rows.Count).End(xlUp).Row)
            Set fnd = target.Find(cel, , , xlWhole)
            If Not fnd Is Nothing Then
                cel.Offset(, -8) = a
                Exit For
            End If
        Next
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

It took about 3 minutes. Overall, still pretty inefficient. Maybe there's a way to improve the VBA, or maybe there's a way to get a sleeker formula. I'd really like to get this down to at least under a minute, if at all possible.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38851365
So, no more ideas for this?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38891790
Hi, Cluskitt.

A few questions, please.
(1) You mention that you fill a column per week. How are these new entries added -
 - Frequent additions of a few entries or occasional additions of lots of entries?
 - Are they typed? Pasted? Something else?
(2) Are existing entries in Folha2 ever changed? Ever moved to a different column?
(3) Similarly for Folha1...
 - Frequent additions/updates of a few entries or occasional additions/updates of lots of entries?
 - Are they typed? Pasted? Something else?
 - Are existing entries in Folha2 ever changed?

Thanks,
Brian.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38892254
1- Usually, each monday I add about 5k rows on a new column. They are pasted from a text file.
2- The existing entries in Folha2 aren't likely to be changed. Meaning, there's a 99% chance they won't change. They are pasted at the beginning of the week and will likely stay that way forever.
3- The list on Folha1 will keep growing. Eventually new values will be added to the bottom of the list, which will also need to check Folha2. Values to this list are pasted from a different excel file.

You can assume that the values in Folha2 and Folha1 are frozen, that is, they don't change. New stuff gets added, but old stuff will remain the same. If there ever is any change, I'll handle that manually or in a way that won't affect the rest.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38892358
Thanks, Cluskitt.

That all sounds encouraging. What about the following?
 - No formulas on Folha1 (or Folha2).
 - You paste the new entries to Folha2 as currently.
 - New Folha1 entries are pasted to Folha3.
 - A macro adds the Folha2 columns to the entries in Folha3.
 - The two columns in Folha3 are copied to Folha1.
 - Folha3 is cleared ready for the next week's data.

Because each Folha1 entry is matched against the columns once and once only, even tens (hundreds?) of columns would be manageable.

Regards,
Brian.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38892693
That's not quite how it works. This is the procedure, step by step:
1- We add values to the list on Folha1. The first time we added a bit over 50k.
2- Each week we add about 5k values to Folha2. These have to be matched to Folha1 as specified above.
3- Every once in a while (there is no preset period for this), we add more values to Folha1. Most likely, these values won't be present on Folha2, but there is a chance that some will, so a check is required.

The only one that is regular is adding a new column to Folha2.

That being said, if you want to create a macro that will use Folha3 to dump additions to Folha1, that is acceptable. I can see no problem in such a procedure. It would actually even be slightly better, as I could just dump the values on A1, instead of having to find the last value on the list. Just keep in mind that additions to Folha2 and Folha1 are independent.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38892814
Cluskitt,

That's not quite how it works.
Yes, I understand that  - this is my proposal for how it would work in the future.

Just keep in mind that additions to Folha2 and Folha1 are independent.
Again, that is included in my proposal.

Most likely, these values won't be present on Folha2, but there is a chance that some will, so a check is required.
OK, this is new. If an entry doesn't exist now, will it ever exist? If so, will it be found in the next 5K addition or couyld it be months later?

Thaks,
Brian.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38892865
Values on Folha1 don't always have a matching value on Folha2. In some cases, they never will. On most cases (99.99%), they eventually will as values are being added to Folha2. It could be next week or months later.

If it helps you understand a bit better, this is sort of a "to contact" list. They are companies that we have to contact and we have a huge list. Every week, some are contacted (which we add to Folha2). Every now and then, we add another batch of contacts (which we add to the bottom of Folha1). We need to know which have been contacted and in which week (which column of Folha2), as well as those that haven't been contacted yet.

Those very few that are on Folha2 and not on Folha1 can be disregarded (as long as they don't interfere with the macro). I will deal with them differently.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38892950
Cluskitt,

Because Folha1 entries may not match, I now think that the processing should be done on additions to Folha2, not Folha1. A quick question first, please, rather than Folha2 having a column per week, why not have two columns - the contact and the week? So, you'd paste the new block into an empty sheet. The macro would then add them to the end of Folha2, giving them the appropriate "week" number (either one supplied by you, or, preferably, one more than the last one used.

This would be a lot more manageable than ever-increasing columns.

Regards,
Brian.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38893117
Sure, that's also acceptable. The end result is the same. I can check individual weeks by filtering the column. And using an increasing number is what I'm using now, so it's also good to increase the last one.

Keep in mind, though, that some mechanism needs to exist to check for values in Folha2 when new values are added to Folha1.

One though occurs, though. If I add values to the bottom now, instead of a new column, wouldn't a simple VLOOKUP or MATCH be better than using a macro? I was having difficulty getting an effective method to match on different columns, but with just one seems a lot simpler, no?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38893566
Cluskitt,

The multiple columns was the source of the issues. Once they're out of the pictures it's simply a matter of the best/easiest/cheapest option.

You add to Folha2 once a week - how often to Folha1?

Thanks,
Brian.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38893743
As I said, there is no defined period for that. So far, we've only added values once, after two weeks of the first list, and only about 1.5k values. We estimate that next month we'll be adding some 20k more. It's possible that there will be a new one about once a month, but that isn't in any way certain. In theory, we should add some more once the list is almost exhausted. At 5k a week, that would be approx. 20k a month. But it is even possible that there will be a hiatus in between (where we add to neither Folha1 or Folha2).
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38893826
Cluskitt,

This feels like one macro that (optionally?) adds to Folha2 and then re-checks any non-matching Folha1.

Regards,
Brian.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38893847
Yes, that would possibly be a good option. Another option would be adding to Folha3, checking those values against non-matching Folha1, then moving them to Folha2.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38893973
Cluskitt,

Another option would be adding to Folha3, checking those values against non-matching Folha1, then moving them to Folha2.
Except that that wouldn't update entries added to Folha1 since the last Folha2 run.

Regards,
Brian.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38894021
Exactly. You would also need a mechanism to add to Folha1 which would do the reverse check. Best would be to use a Folha4, I guess, seeing as, in the real example, Folha1 has more columns which have to be pasted.
Any solution that requires on static data (as opposed to formulas that update on paste) would require both checks.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 26

Expert Comment

by:redmondb
ID: 38894053
Cluskitt,

Much simpler is my last proposal! I'll put it together and you'll see how it behaves.

Regards,
Brian.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38894074
Ok. I can work a couple solutions based on either formula or code, but I want to see yours, in case it's faster or simpler. Also, I won't have time to work on this until next wednesday, most likely.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38896289
This is a much user-defined function.  You pass it a value to find and a range.  It will return the cell reference where the value is found.
Option Explicit

Public Function FindIt(parmFindWhat, parmWhereToLook As Range) As Range
    Set FindIt = parmWhereToLook.Find(parmFindWhat)
End Function

Open in new window

Example:
Given the following data layout, we want to find the column where the value in cell A2 ("BCD") is found in the cells to the right.
BCD	ABC	GHI	MNO	STU	YZA	EFG	KLM	QRS
	DEF	JKL	PQR	VWX	BCD	HIJ	NOP	TUV

Open in new window

Invoking the user-defined macro in a formula, we then apply the Column() function to the result and the result is 6
=COLUMN(FindIt(A2,B2:I3))

Open in new window

Note: If the searched-for value is not found, then the result will be: #VALUE!
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38900765
Ok, that works pretty well. Except for one thing: whenever I do any filtering, insert/remove other columns or otherwise do anything on the file, it will calculate again. This gets to be annoying really fast and I can't turn off auto calc (need it for other formulas on the book).

Any solution for this?
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38901861
Cluskitt,

Please see the attached. A few points...
(1) When you click on the blue button, the macro first checks "Folha3". If there are any values there then it adds them to Folha2 (and giving them a Week No. one more than the last one used) - and then clears "Folha3" to be ready for the next batch.
Please note that the macro doesn't mind if "Folha3" is empty.
(2) The macro then checks every entry in "Folha1" which has a blank in column B.
(3) At the moment the file has week 2 ready to be processed in "Folha3", so just press the blue button. Now, copy the week 3 data from "Folha2 - Old" to the first column of "Folha2" and press the blue button again. All three weeks' data is now in "Folha2" and the matching entries in "Folha!" have been updated.

So, you've got a single button which will handle the following situations...
(1) New data in "Folha3" to be added to "Folha2". A recheck to be done of Folha1's blanks.
(2) No new data for "Folha2", but you want to recheck the blanks in "Folha1" - presumably because you've just added some entries to it. (Or you may have manually updated some existing entries - just clear their Week No.)

Please note that when the macro isn't actually running there are no formulas on any of the sheets and so no overhead.

The code is...
Option Explicit

Sub Update_Contacts()
Dim xContacted As Worksheet
Dim xCompanies As Worksheet
Dim xAdditions As Worksheet
Dim xBlank     As Range
Dim xLast_Comp As Long
Dim xLast_Cont As Long
Dim xLast_Add  As Long
Dim xResponse  As Long

Set xCompanies = Sheets("Folha1")
Set xContacted = Sheets("Folha2")
Set xAdditions = Sheets("Folha3")

xLast_Cont = xContacted.UsedRange.Cells(1, 1).Row + xContacted.UsedRange.Rows.Count - 1
xLast_Comp = xCompanies.UsedRange.Cells(1, 1).Row + xCompanies.UsedRange.Rows.Count - 1
xLast_Add = xAdditions.UsedRange.Cells(1, 1).Row + xAdditions.UsedRange.Rows.Count - 1

' Is there data to add to xContacted?
If xLast_Add = 1 And xAdditions.Range("A1") = "" Then
    xResponse = MsgBox("No data found in """ & xAdditions.Name & """ - " & Chr(10) & Chr(10) _
                        & "('OK' to just refresh & """ & xContacted.Name & """, 'Cancel' to Quit.)", vbOKCancel, "Update Contacts")
    If xResponse = 2 Then
        MsgBox ("USer chose to quit - run terminating.")
        Exit Sub
    End If
    xLast_Add = 0
End If

Application.ScreenUpdating = True
    
    If xLast_Add <> 0 Then
        ' Yes, there is data to be added...
        xAdditions.Range("A1:A" & xLast_Add).Copy Destination:=xContacted.Cells(xLast_Cont + 1, 1)
        xContacted.Range(Cells(xLast_Cont + 1, 2), Cells(xLast_Cont + xLast_Add, 2)) = 1 + WorksheetFunction.Max(0, xContacted.Cells(xLast_Cont, 2))
        xLast_Cont = xLast_Cont + xLast_Add
        ' And clear the xAddtions sheet...
        xAdditions.Range("A1:A" & xLast_Add).EntireRow.Delete Shift:=xlShiftUp
        If xAdditions.UsedRange.Rows.Count < 1 Then Debug.Print "?!" 'Force Excel to recalculate the last cell.
    End If
    
    ' Find Blank cells in xCompanies
    On Error Resume Next
        Set xBlank = xCompanies.Range("B2:B" & xLast_Comp).SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If xBlank Is Nothing Then
        MsgBox ("No unmatched data found in """ & xCompanies.Name & """ - run terminated." _
            & Chr(10) & IIf(xLast_Add = 0, "No updates made to """ & xCompanies.Name & """.", """" & xContacted.Name & """ updated and """ & xAdditions.Name & """ cleared."))
        Application.ScreenUpdating = True
        Exit Sub
    End If

    xBlank.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1]," & xContacted.Name & "!R2C1:R" & xLast_Cont & "C2,2,0),"""")"
    xBlank.Formula = xBlank.Value

Application.ScreenUpdating = True

MsgBox ("Updates complete.")

End Sub

Open in new window

Regards,
Brian.Sample--5----V2.xlsm
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38901952
In my FindIt() user-defined solution, you can supply a bigger range of cells than are currently populated with data, in anticipation of later data growth.
Example of more rows and columns:
=COLUMN(FindIt(A2,B2:GG3000))

Open in new window

0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38902004
@aikimark: I did that. However, when I'm changing anything on the first sheet, whether it's a filter or similar, it's constantly recalculating.

@redmondb: I will try your solution soon, as soon as I have some free time.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38902573
You can change the calculation to manual and perform the calculation as you need it, pressing the F9 key.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38902677
Thanks, Cluskitt. (BTW, mine is dynamic - it automatically uses the required no. of entries on each of the three sheets.)
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38903014
aikimark: I can't turn off auto calc because it's required for other formulas on the sheet (some of which actually rely on this formula). Is there any way to make it only update on changes to Folha2 (or on autofill in Folha1)?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38903648
The autocalculation setting (=true) should only affect the dependents of cells that you change.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38913410
I haven't yet had time to work on this. Just wanted to inform that this hasn't been forgotten.
0
 
LVL 18

Author Closing Comment

by:Cluskitt
ID: 38926291
Finally had time to check this. I had to change the ranges in your code (xlBlank checks col A not B, and the RC reference in the lookup was changed from -1 to 8).

It works perfectly and takes less time than any of the solutions presented. It also pastes as value, meaning that further calculations and filters aren't hindered. This was great. Thanks a lot for your help.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38926330
Actually, for whatever reason, it inserted some zeroes. The way I did this was, I had 3 weeks already inserted, placed the 4th on Folha3, cleaned row A on Folha1. I then ran this twice (once with values on Folha3, once without). When I checked it now, there were some that had value 0. However, when I deleted them and ran it again (with Folha3 empty), things seemed normal.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38926375
Also, excel wasn't fond of the direct range reference on the xContacted.Range line in the data to be added block. I had to add a xContacted.Activate before it, otherwise it would throw an exception (solved by switching the book to Folha2). Also added a xCompanies.Activate after the block.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38927129
Thanks, Cluskitt.

I had to change the ranges in your code
I presume because the actual layout is different from your example?

Actually, for whatever reason, it inserted some zeroes.
Can you give me an example, please?

cleaned row A on Folha1.
I presume you mean column, but I don't understand "clean".

I had to add a xContacted.Activate before it
Yes, the macro assumed it was being run from the button on that sheet. (BTW, xCompanies.Activate shouldn't be necessary.)

Thanks,
Brian.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38928875
1- Yes, the layout is different
2- I can't really give you an example, because when I tried to duplicate it, it didn't happen. It was just that one time, so I just ignored it from then on.
3- Actually, it was two typos. It was indeed column, and I meant cleared, not cleaned
4- I'm running the macro from a hotkey combo. xCompanies.Activate might not be necessary, but I actually changed to xCompanies.Select. It never hurts to force focus and it is handy to leave that sheet selected.

Thank you for your assistance.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38936186
Thanks, Cluskitt

(3) It was indeed column, and I meant cleared, not cleaned
Why would you ever clear Folha1's column A? I could perhaps understand clearing Column B if you want to refresh values, but not Column A.

Correct me if I'm wrong, but it appears that you had some finger-trouble updating the macro to reflect your actual file - the file provided worked fine for you but you got some funny results with your live file?

Right now, what problems do you have with the live file?

Thanks,
Brian.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38937148
No, I meant A. Column A, which is where it now writes the value from Folha2, had some trace values from previous formula tests. So I cleared it and let the macro fill it. It was a first run of sorts.

It's possible that I didn't clear it properly. As I said, I couldn't replicate the problem. No more 0's appeared on the file. As of now, everything seems to be working properly. There seem to be no problems on the live file.

Once again, thanks for your assistance.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38937507
Thanks, Cluskitt, I had forgotten that I was dealing with a simplified layout.

There seem to be no problems on the live file.
Please feel free to post here if it starts to misbehave.

Thanks,
Brian.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38950285
Ok, there is something weird going on. Today I added this week's values to Folha3. I ran the macro and it didn't update the values on Folha1. It moved the values to Folha2, but Folha1 was in the same state as before. So I placed a breakpoint after the formula and, after the processor finished calculating, I checked that the values were there. The next line (.Value) runs and they're gone.

If I clear the column, it calculates everything fine. Any idea why?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 38950764
does my function behave any differently?
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38950791
Nope. Trying that or the chained lookup, it works fine.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38956180
Cluskitt,

it didn't update the values on Folha1
Please post your actual code here.

Thanks,
Brian.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 38956888
The code is pretty much the same. I just changed it to reflect the columns of the real case:
Option Explicit

Public Function FindIt(parmFindWhat, parmWhereToLook As Range) As Range
    Set FindIt = parmWhereToLook.Find(parmFindWhat, , , , xlByColumns)
End Function

Sub Update_Contacts()
    Dim xContacted As Worksheet
    Dim xCompanies As Worksheet
    Dim xAdditions As Worksheet
    Dim xBlank     As Range
    Dim xLast_Comp As Long
    Dim xLast_Cont As Long
    Dim xLast_Add  As Long
    Dim xResponse  As Long
    
    Set xCompanies = Sheets("Folha1")
    Set xContacted = Sheets("Folha2")
    Set xAdditions = Sheets("Folha3")
    
    xLast_Cont = xContacted.UsedRange.Cells(1, 1).Row + xContacted.UsedRange.Rows.Count - 1
    xLast_Comp = xCompanies.UsedRange.Cells(1, 1).Row + xCompanies.UsedRange.Rows.Count - 1
    xLast_Add = xAdditions.UsedRange.Cells(1, 1).Row + xAdditions.UsedRange.Rows.Count - 1
    
    ' Is there data to add to xContacted?
    If xLast_Add = 1 And xAdditions.Range("A1") = "" Then
        xResponse = MsgBox("No data found in """ & xAdditions.Name & """ - " & Chr(10) & Chr(10) _
                            & "('OK' to just refresh """ & xContacted.Name & """, 'Cancel' to Quit.)", vbOKCancel, "Update Contacts")
        If xResponse = 2 Then
            MsgBox ("User chose to quit - run terminating.")
            Exit Sub
        End If
        xLast_Add = 0
    End If
    
    Application.ScreenUpdating = True
        
        If xLast_Add <> 0 Then
            ' Yes, there is data to be added...
            xAdditions.Range("A1:A" & xLast_Add).Copy Destination:=xContacted.Cells(xLast_Cont + 1, 1)
            xContacted.Activate
            xContacted.Range(Cells(xLast_Cont + 1, 2), Cells(xLast_Cont + xLast_Add, 2)) = 1 + WorksheetFunction.Max(0, xContacted.Cells(xLast_Cont, 2))
            xLast_Cont = xLast_Cont + xLast_Add
            ' And clear the xAddtions sheet...
            xAdditions.Range("A1:A" & xLast_Add).EntireRow.Delete Shift:=xlShiftUp
            If xAdditions.UsedRange.Rows.Count < 1 Then Debug.Print "?!" 'Force Excel to recalculate the last cell.
        End If
        
        xCompanies.Select
        ' Find Blank cells in xCompanies
        On Error Resume Next
            Set xBlank = xCompanies.Range("A2:A" & xLast_Comp).SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        If xBlank Is Nothing Then
            MsgBox ("No unmatched data found in """ & xCompanies.Name & """ - run terminated." _
                & Chr(10) & IIf(xLast_Add = 0, "No updates made to """ & xCompanies.Name & """.", """" & xContacted.Name & """ updated and """ & xAdditions.Name & """ cleared."))
            Application.ScreenUpdating = True
            Exit Sub
        End If
    
        xBlank.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[8]," & xContacted.Name & "!R2C1:R" & xLast_Cont & "C2,2,0),"""")"
        xBlank.Formula = xBlank.Value
    
    Application.ScreenUpdating = True
    
    MsgBox ("Updates complete.")
End Sub

Open in new window

Placing a breakpoint in the end, on the xBlank.Formula = xBlank.Value, I can see that the values from Folha3 have been moved to Folha2 and given a value (5). The formula, once it calculates, attributes the value to their respective rows. Once I run this line, though, the value is gone.

If I clear the whole column and recalculate, then everything is fine and the value will remain there, which is what I ended up doing. It does take longer, so I didn't want to have to do that all the time, especially because, as far as I can see, it makes no sense that it's doing that. It should work fine. Unless we need a copy/paste special?
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 39066012
I can confirm that this continues to happen. If I run the macro with the previous values on the column, the new values aren't updated (or are updated and then cleared). If I clear the whole column, it will update all values, including the new ones.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now