Solved

# Excel: Identify which column has the desired value

Posted on 2013-01-29
344 Views
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
Question by:Cluskitt
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 27
• 14
• 5
• +2

LVL 23

Expert Comment

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

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

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

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

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

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

ID: 38832087
No attachment
0

LVL 18

Author Comment

ID: 38834292
Oops... sorry :P
Sample.xlsx
0

LVL 23

Expert Comment

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

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

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
``````
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

ID: 38851365
So, no more ideas for this?
0

LVL 26

Expert Comment

ID: 38891790
Hi, Cluskitt.

(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...
- Are they typed? Pasted? Something else?
- Are existing entries in Folha2 ever changed?

Thanks,
Brian.
0

LVL 18

Author Comment

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

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

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

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

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

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

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

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

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

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

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

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

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

LVL 26

Expert Comment

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

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

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
``````
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
``````
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))
``````
Note: If the searched-for value is not found, then the result will be: #VALUE!
0

LVL 18

Author Comment

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

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 xBlank     As Range
Dim xLast_Comp As Long
Dim xLast_Cont As Long
Dim xResponse  As Long

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

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

' Is there data to add to xContacted?
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
End If

Application.ScreenUpdating = True

' Yes, there is data to be added...
xContacted.Range(Cells(xLast_Cont + 1, 2), Cells(xLast_Cont + xLast_Add, 2)) = 1 + WorksheetFunction.Max(0, xContacted.Cells(xLast_Cont, 2))
' And clear the xAddtions sheet...
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

End Sub
``````
Regards,
Brian.Sample--5----V2.xlsm
0

LVL 45

Expert Comment

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))
``````
0

LVL 18

Author Comment

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

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

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

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

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

LVL 18

Author Comment

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

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

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

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

ID: 38927129
Thanks, Cluskitt.

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".

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

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.

0

LVL 26

Expert Comment

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

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

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

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

ID: 38950764
does my function behave any differently?
0

LVL 18

Author Comment

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

LVL 26

Expert Comment

ID: 38956180
Cluskitt,

it didn't update the values on Folha1

Thanks,
Brian.
0

LVL 18

Author Comment

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 xBlank     As Range
Dim xLast_Comp As Long
Dim xLast_Cont As Long
Dim xResponse  As Long

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

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

' Is there data to add to xContacted?
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
End If

Application.ScreenUpdating = True

' Yes, there is data to be added...
xContacted.Activate
xContacted.Range(Cells(xLast_Cont + 1, 2), Cells(xLast_Cont + xLast_Add, 2)) = 1 + WorksheetFunction.Max(0, xContacted.Cells(xLast_Cont, 2))
' And clear the xAddtions sheet...
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

End Sub
``````
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

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
###### Suggested Courses
Course of the Month6 days, 21 hours left to enroll