# SUMIF formula to VBA

September 17, 2015 at 10:49:31
Specs: Windows 7
 =IF(SUMIFS(FTMRC,FTMRC,VALUE(40),CTN,RPA!E5),40,IF(SUMIFS(FTMRC,FTMRC,VALUE(45),CTN,RPA!E5),45,IF(SUMIFS(FTMRC,FTMRC,VALUE(30),CTN,RPA!E5),30,IF(SUMIFS(FTMRC,FTMRC,VALUE(35),CTN,RPA!E5),35,IF(SUMIFS(FTMRC,FTMRC,VALUE(20),CTN,RPA!E5),20,IF(SUMIFS(FTMRC,FTMRC,VALUE(15),CTN,RPA!E5),15,IF(SUMIFS(FTMRC,FTMRC,VALUE(50),CTN,RPA!E5),50,IF(SUMIFS(FTMRC,FTMRC,VALUE(64.99),CTN,RPA!E5),64.99,IF(SUMIFS(FTMRC,FTMRC,VALUE(65),CTN,RPA!E5),65,IF(SUMIFS(FTMRC,FTMRC,VALUE(10),CTN,RPA!E5),10,VALUE(0)))))))))))I have this formula and it works well. Does exactly what I need. However I am trying to rework the spreadsheet in VBA and I can't quite replicate it.Someone said I would make this formula way more efficent using Case and looping. Basically,FTMRC = sheet4 columnAMCTN = sheet4 columnQ

See More: SUMIF formula to VBA

#1
September 17, 2015 at 13:13:41
 I'm not sure what you mean by "rework the spreadsheet in VBA". Are you trying to create a User Defined Function (UDF) to replace that formula? If so, could you explain the "goal" of the formula, perhaps by supplying some example input data and the expected result based on that data so that we can get a better understanding of what you are trying to accomplish?Please click on the following line and read the instructions on how to post example data in this forum. Thanks!

Report •

#2
September 17, 2015 at 14:03:36
 The goal of the formula is to look for pricing.`K (Number/CTN) AM (pricing/FTMRC)` `number 1 0``number 1 0``number 1 0 ``number 1 40``number 2 40``number 2 3``number 2 6``number 2 0``number 2 0``number 3 9.99``number 3 40``number 3 0`etc The goal being to pull just the numbers shown above. Once pulled, it should post to sheet3 so that the number aligns with the cost.message edited by LightKerosene

Report •

#3
September 17, 2015 at 17:01:58

Report •

Related Solutions

#4
September 18, 2015 at 08:55:22
 I'll try to explain again.65,64.99,40,45,30,35,15,20,10,50 < These are all the possible values from Column AM(pricing/FTMRC)There is a identification number in column K(Number/CTN) that varies in how many times it duplicated based on how many features this user has on their account.What the code does is, searches column K to make sure that it matches the phone number on another sheet (RPA!E5), then writes the Pricing/MRC that it pulled up and puts it in the same row as the line on the other sheet. Aligning the found number or MRC with the ID number from the first tab. Usually I can just copy the first thing that pulls up with a Vlookup or something, but because the number on the report duplicates and random in column K I have to use a Sumif formula.RPA!E5 is the first line in the final product.I need it to search for each phone number in RPA!E, until done.

Report •

#5
September 21, 2015 at 06:25:58
 I would like to help, but I'm just not seeing it.First you say: "There is a identification number in column K"Then you say "searches column K to make sure that it matches the phone number on another sheet (RPA!E5)"Are you saying that the identification number and phone number are the same thing, i.e. the user is identified by his phone number?Let me see if I can state your objective as I think I understand it.- You have a list of unique Phone Numbers in RPA Column E- You have a list of (possibly) repeating Phone Numbers in Column K of another sheet.- You have a list of Pricing values in Column AM of the same "another sheet" that contains the Column K phone numbers.- You want to pull the Pricing values for each Phone number into the RPA sheet.If that is correct, then I'm missing the part related to how you want to associate (possibly) multiple Pricing values to a single entry of a Phone number in the list on RPA Column E. Would the multiple values go in sequential columns?

Report •

#6
September 21, 2015 at 08:49:21
 Yes I have Phone numbers in column RPA!E, I want to cross reference this with Column K on another sheet, in order to get the pricing, one of the numbers I listed because those arent the only pricing. I want it to put that number on the RPA tab.Ex. Check Phone number E5, go to Feature Report Column K and make sure that phone number is = to the number in E5, then check AM for 65,64.99,40,45,30,35,15,20,10,50, if value is found put that value in RPA!v5, then move on to E6 and repeat until all numbers in column E of RPA have been filled.I don't know how else to explain it. message edited by LightKerosene

Report •

#7
September 21, 2015 at 10:23:29
 The part that is still confusing me is your 2 lists of phone numbers, specifically the fact that mentioned that there may be duplicates numbers in Feature_Report!K. Allow me to use an example to help explain my confusion...You said: "Check Phone number E5, go to Feature Report Column K and make sure that phone number is = to the number in E5" OK, here's an example...RPA!E5 contains 412-555-9456Feature_Report!K7 contains 412-555-9456, therefore we have a match. So far, so good.You said: "then check AM for 65,64.99,40,45,30,35,15,20,10,50, if value is found put that value in RPA!v5"OK, in my example, let's say Feature_Report!AM7 contains 64.99. In that case, the code should put 64.99 in RPA!V5.That seems to make sense and can easily be done with VBA.The confusion comes from the fact that you said that there may be/are duplicate phone numbers in Feature_Report!K:K.Combining the example you posted in Response # 2 with my example data, it appears that Feature_Report!K8:10 also contain 412-555-9456 (You have "number 1" listed 4 times in your Column K). I now have 4 matches for the phone number in RPA!E5. Let's say AM9 contains 45 which is also on your list. Where on the RPA sheet should the code put that value? RPA!V5 already contains 64.99 from the match in Feature_Report!K7, so the code can't put the 45 there. Where should it go?Do you see my confusion?I also made an assumption in my previous response that you did not confirm or deny:"You have a list of unique Phone Numbers in RPA Column E."Is this true? If not, i.e. if 412-555-9456 appears more than once in RPA!E, how do we deal with multiple entries of the same phone number in both RPA!E and Feature_Report!K?

Report •

#8
September 21, 2015 at 10:43:35
 It will only have one of those at a time. Yes there is a unique list in column E.The feature report varies based on the itemID so the number in column E5 could repeat itself 20 times on that report where as the number on column E6 only repeats 5 times. The reason those numbers are picked is those are the varying prices for Data plans. I only need it to pull the fist time of seeing one of those numbers then move on to the next phone number. Sort of like a Vlookup.

Report •

#9
September 21, 2015 at 12:20:00
 Try this...I don't know if I got the Sheet Names correct, so you may have to modify them:RPA & Feature_Report```Option Explicit Sub FeatureReportValues() Dim last_srcRw As Long, srcRw As Long Dim firstAddress As String Dim phNum As Range 'Determine last cell with value in RPA!E:E last_srcRw = Sheets("RPA").Range("E" & Rows.Count).End(xlUp).Row 'Set Feature_Report!K:K as search range With Sheets("Feature_Report").Range("K:K") 'Loop through RPA!E:E For srcRw = 5 To last_srcRw 'Find Phone Number in Feature_Report!K:K Set phNum = .Find(Sheets("RPA").Range("E" & srcRw)) 'If match is found, check value in Column AM against list If Not phNum Is Nothing Then firstAddress = phNum.Address Do Select Case Sheets("Feature_Report").Range("AM" & phNum.Row) Case 65, 64.99, 40, 45, 30, 35, 15, 20, 10, 50 'If value from list is found, copy value to RPA!V Sheets("RPA").Range("V" & srcRw) = _ Sheets("Feature_Report").Range("AM" & phNum.Row) Exit Do End Select 'If value from list not found, seach for next ccurrence of same Phone Number Set phNum = .FindNext(phNum) Loop While Not phNum Is Nothing And phNum.Address <> firstAddress End If Next End With End Sub```message edited by DerbyDad03

Report •

#10
September 21, 2015 at 13:07:04
 Removedmessage edited by LightKerosene

Report •

#11
September 21, 2015 at 13:46:41
 Please remember to use the pre tags when posting data and/or VBA code in this forum. Once again, the instructions can be found via the link at the bottom of my posts.You said: "This is not working. It didn't paste anything in V. "Did the code do anything? Did it produce an error? What exactly happened when you ran it? Since I do not have a copy of your workbook to actually test the code on, the best I can do is tell you how I tested it. If my example workbook layout is not similar to yours, it will be up to you to tell me what is different.Please understand that you have a copy of your workbook, you know exactly how it is set up, you know exactly what your data looks like, you know the names of your sheets, etc. Basically, you know everything about your data from the input to the desired output. All we have is what we can glean from your posts and then we have to try to replicate your situation from afar. It does not always go smoothly. One option for you to consider is to post a copy of your workbook on a share site and then post a link back here. Obviously you should remove any personal data that you don't want us to see, but at least we would be working with almost exactly what you are working with.OK, I started with this data layout:Sheet RPA1 - A list of unique numbers starting in E5:2 - A blank Column V``` E V 5 45 6 46 7 47 8 48 9 49``` Sheet Feature_Report1 - A list of repeating numbers in Column K, all of which have a matching value in RPA Column E.2 - A list of values in Column AM, some of which are contained in list of values that you are trying to match (65, 64.99, 40, 45, 30, 35, 15, 20, 10, 50)``` K AM 5 45 52 6 45 40 7 45 4 8 45 15 9 46 6 10 46 30 11 46 8 12 47 9 13 47 64.99 14 47 11 15 48 12 16 48 35 17 48 35 18 48 35 19 49 35 20 49 40 21 49 35 22 49 35 23 49 35 ```When I run the code, Sheet RPA ends up looking like the following. As far as I can tell, that is what you asked for.``` E V 5 45 40 6 46 30 7 47 64.99 8 48 35 9 49 35``` It's up to you to tell us why that is not the correct output.

Report •

#12
September 21, 2015 at 14:23:55
 https://drive.google.com/file/d/0B9...Smart. Download it. I made sure to save it on the Macro settings tab so that you can test what I am doing kind of. I am redoing the current macros in VBA as well once I figure out how, I want them to be able to but unlimited lines instead of 150 or whatever it is.But for this thread I am focused solely on The columns I told you about.

Report •

#13
September 21, 2015 at 16:32:05
 I have no idea if the workbook you posted is the same workbook that you tested the code on, but if it is, it should be obvious why it didn't work.There is no data in RPA!E for the code to search for. I thought there was supposed to be a list of phone numbers in that column? Why is it blank?All I did was copy a number from the Feature Report sheet (5555555555555) into RPA!E5 and the code placed a 40 in RPA!V5.Actually, I did have to make one change to the code: You missed one occurrence of Feature_Report so the code threw out a "Subscript Out Of Range" error. (BTW, I do recall asking you if the code produced any errors. You didn't answer that question. Just so you know, I ask questions for a reason. The answers often give me a place to start looking for problems.)Another thing that you didn't mention was that phone numbers in RPA!E only extend down to E128. That makes a difference in how the code is written, but was not the reason the code didn't work for you. However, knowing that it makes it easier to write code that is more efficient.In any case, this code works in the workbook you provided, after I put some phone numbers in RPA!E5:E128 to give the code something to search for.```Option Explicit Sub FeatureReportValues() Dim last_srcRw As Long, srcRw As Long Dim firstAddress As String Dim phNum As Range 'Set Feature_Report!K:K as search range With Sheets("Feature Report").Range("K:K") 'Loop through RPA!E5:E128 For srcRw = 5 To 128 'Make sure the cell is not empty If Sheets("RPA").Range("E" & srcRw) <> "" Then 'Find Phone Number in Feature Report!K:K Set phNum = .Find(Sheets("RPA").Range("E" & srcRw)) 'If match is found, check value in Column AM against list If Not phNum Is Nothing Then firstAddress = phNum.Address Do Select Case Sheets("Feature Report").Range("AM" & phNum.Row) Case 65, 64.99, 40, 45, 30, 35, 15, 20, 10, 50 'If value from list is found, copy value to RPA!V Sheets("RPA").Range("V" & srcRw) = _ Sheets("Feature Report").Range("AM" & phNum.Row) Exit Do End Select 'If value from list not found, seach for next ccurrence of same Phone Number Set phNum = .FindNext(phNum) Loop While Not phNum Is Nothing And phNum.Address <> firstAddress End If End If Next End With End Sub```message edited by DerbyDad03

Report •

#14
September 22, 2015 at 09:00:16
 The code goes as far as needed up to 3000. 128 was just an example.I left it on the macro settings spot for you, Cntrl shift Q will import all the data for you so that you can write the script. My apologies for not explaining.The only issue I see now is that once it finds the number it doesn't move on to the next number.Thank you for your help so far we are making progress.

Report •

#15
September 22, 2015 at 11:06:22
 I can't work on the file until this evening EST.re: The only issue I see now is that once it finds the number it doesn't move on to the next number.That is not what I see when I test it with my own data. I put in about 10 different phone numbers in RPA!E and then replicated those numbers multiple times with varying counts in Feature Report!K. The code pulled in the correct "list" values from AM for each number.I'll have to see what it does with your data and take it from there.

Report •

#16
September 22, 2015 at 14:16:27
 Also if you happen to know. The actual spreadsheet starts with 3000 lines, can we make it so that it looks for the last number in column E + 5 lines and Deletes the rest. Right now I have to manually do it because I haven't found a code that seems to be compatible with that table formatting.Again thanks for the help. If this code is too much I will figure it out another time or keep searching.I'd imagine it is something like.Last RPA!E5:E3004Cntrl shift downDown 5xthen select blanks and delete those rows.Using macro recorder.message edited by LightKerosene

Report •

#17
September 22, 2015 at 15:07:07
 I'm curious as to why you marked the thread as solved if you are still having problems with the code.

Report •

#18
September 22, 2015 at 15:39:35
 Was a misclick. Also it was best answer so far.

Report •

#19
September 22, 2015 at 16:09:50
 I have reset the best answer. Let's wait until we're done to mark it solved.

Report •

#20
September 22, 2015 at 16:14:03
 https://drive.google.com/file/d/0B9...I redid the spreadsheet for you. The macro takes forever cause it isn't coded correctly but it was done in macro recorder because that is the most consistent way I know how to get it to work. Control + Shift + Q imports all of the information you need.Basically I want it to import the information, unhide all the rows. Figure out what the last phone number is, skip 5 lines then Delete the rest, not hide.Once we finish finding these numbers I will be using it as a baseline to do the macro for all of the features and such.Feel free to assist with whatever is easy but my main concern is the one that I started the forum with, which is finding those charges and putting them in Column V.

Report •

#21
September 22, 2015 at 17:13:48
 re: "Cntrl shift Q will import all the data for you so that you can write the script."Just for future reference, VBA code is typically referred to as a Macro, not a script. There is a difference. When I press Ctrl Shift Q I am presented with the following error:```Run-time Error '1004': Method 'Range' of object '_Global' failed```Clicking Debug takes me to the Step 2 macro and highlights the following line:` Range("Table5[Monthly Cost]").Select`If I force the code to skip that instruction, I get the same error at:`Range("Table5[Monthly Credits]").Select`I get the same error for the following 3 instructions:```Range("Table5[International Costs]").Select Range("Table5[Additional Line Cost]").Select Range("Table5[Office @ Hand]").Select```After struggling to get the code to complete by skipping those instructions, I see the RPAE5:E19 contains the following list of phone numbers:```5555555555 5555555556 5555555557 5555555558 5555555559 5555555560 5555555561 5555555562 5555555563 5555555564 5555555565 5555555566 5555555567 5555555568 5555555569 5555555570 5555555571 5555555572 5555555573 ```You will note that the first number is 5555555555. Keep that in mind.If I then Unhide the Feature Report sheet, I see that K2:K405 all contain 5555555555. Therefore the only number that the code will find is the first occurrence of 5555555555 with a matching list value because that is what you asked for:"I only need it to pull the fist time of seeing one of those numbers then move on to the next phone number"The code finds the first occurrence of 5555555555 with a matching value from the list (K8 = 5555555555, AM8 = 40). It places the 40 in RPA!V5 and then attempts the find the other numbers, one at a time. Since there are no other matching numbers, it only appears to stop at the first number. If you were to use F8 to Single Step through the code, and set a Watch on what the code is searching for, you will find that it searches for every number in E5:E19. It never finds any other numbers because they don't exist in Feature Report!K.I am not going to work on your other issues until we get this part working. Fix the errors in the Step 2 macro and then fill Column K with more than just 5555555555 to at least prove to yourself that the code I offered does indeed work. When we are on the same page as far as that goes, we'll move on to your other issues.

Report •

#22
September 22, 2015 at 17:18:53
 We seem to have crossed posts. I will now need to test your new workbook to see what happens. Please don't change anything else until you hear from me again.

Report •

#23
September 22, 2015 at 17:28:11
 I still get an error at:`Range("Table5[Office @ Hand]").Select`You still have nothing except for 5555555555 in Feature Report!K.Change any of those Feature Report!K numbers to a matching number from RPA!E5:E19, place a number from your list in the same row in Column AM and run my code again. It works for me every time.

Report •

#24
September 23, 2015 at 10:02:58
 Well it looks like we found the issue The phone numbers are in Column Q... I am testing now.

Report •

#25
September 23, 2015 at 10:07:02
 ```Option Explicit Sub FeatureReportValues() Dim last_srcRw As Long, srcRw As Long Dim firstAddress As String Dim phNum As Range 'Set Feature_Report!Q:Q as search range With Sheets("Feature Report").Range("Q:Q") 'Loop through RPA!E5:E3004 For srcRw = 5 To 3004 'Make sure the cell is not empty If Sheets("RPA").Range("E" & srcRw) <> "" Then 'Find Phone Number in Feature Report!Q:Q Set phNum = .Find(Sheets("RPA").Range("E" & srcRw)) 'If match is found, check value in Column AM against list If Not phNum Is Nothing Then firstAddress = phNum.Address Do Select Case Sheets("Feature Report").Range("AM" & phNum.Row) Case 65, 64.99, 40, 45, 30, 35, 15, 20, 10, 50 'If value from list is found, copy value to RPA!V Sheets("RPA").Range("V" & srcRw) = _ Sheets("Feature Report").Range("AM" & phNum.Row) Exit Do End Select 'If value from list not found, seach for next ccurrence of same Phone Number Set phNum = .FindNext(phNum) Loop While Not phNum Is Nothing And phNum.Address <> firstAddress End If End If Next End With End Sub ```message edited by LightKerosene

Report •

#26
September 23, 2015 at 10:22:22
 So question, I figured some of it out. How would I add search column AI for "MSD*" and put the number shown next to it in AM into column RPA AO. So it would function the same, Find number in both RPA and Feature Report. Then go to the Column AI Return AO. I hope that makes sense. I would imagine I just change Case 65 etc to MSD and range AM to AO right?

Report •

#27
September 23, 2015 at 10:24:32
 And once again you posted VBA code without using the pre tags. I've mentioned that at least 3 times. I'm not quite sure why you won't honor that request, but it's bound to impact the amount of help I offer going forward.I see that you changed the code instructions, but not the comments. That is sure to cause confusion later on.message edited by DerbyDad03

Report •

#28
September 23, 2015 at 10:39:56
 I posted it exactly as it looks in my code? Was I supposed to color it?I fixed it with pretags I think. message edited by LightKerosene

Report •

#29
September 23, 2015 at 10:48:49
 Please read the instructions found via the link at the bottom of every one of my posts. The instructions explain how to use the pre tags to post data and VBA code so that the columns line up and the indentations are retained.Take a look at the code I post compared to the code you post. Which do you find easier to read and follow? It is the use of the pre tags that will allow you to copy code from the VBA editor into this forum and retain the indentation. In addition, when you copy properly formatted code from this forum into the VBA editor, the indentations will be retained.Click here:

Report •

#30
September 23, 2015 at 10:50:46
 re: "I fixed it with pretags I think. "Sort of. You had already lost the indentations. The pre tags won't put them back in, they will only retain them if they already exist.

Report •

#31
September 23, 2015 at 10:58:31
 I didn't lose them, the no indentation is just easier for me to read for some reason. I have both.

Report •

#32
September 23, 2015 at 11:02:42
 I noticed I cannot just copy the code and put the same code with different variables underneath, this means I'll have to run different macros for each value on the spreadsheet. Am I wrong? Also if that's the case I could do a macro to run macros one after another so that the user doesn't have to hit cntrl+shift Q, Cntrl + Shift W. But that part is easy, I just didn't know if there was a way to do it one after another like in my other recorded macro.

Report •

#33
September 23, 2015 at 11:39:07

Report •

#34
September 23, 2015 at 11:40:57
 re: "I noticed I cannot just copy the code and put the same code with different variables underneath, this means I'll have to run different macros for each value on the spreadsheet. "I have no idea what you mean by that. "put the same code with different variables underneath"Underneath what? What different variables? You've totally lost me now.

Report •

#35
September 23, 2015 at 11:58:26
 ```Option Explicit Sub FeatureReportValues() Dim last_srcRw As Long, srcRw As Long Dim firstAddress As String Dim phNum As Range 'Set Feature_Report!Q:Q as search range With Sheets("Feature Report").Range("Q:Q") 'Loop through RPA!E5:E3004 For srcRw = 5 To 3004 'Make sure the cell is not empty If Sheets("RPA").Range("E" & srcRw) <> "" Then 'Find Phone Number in Feature Report!Q:Q Set phNum = .Find(Sheets("RPA").Range("E" & srcRw)) 'If match is found, check value in Column AM against list If Not phNum Is Nothing Then firstAddress = phNum.Address Do Select Case Sheets("Feature Report").Range("AM" & phNum.Row) Case 65, 64.99, 40, 45, 30, 35, 15, 20, 10, 50 'If value from list is found, copy value to RPA!V Sheets("RPA").Range("V" & srcRw) = _ Sheets("Feature Report").Range("AM" & phNum.Row) Exit Do End Select 'If value from list not found, seach for next ccurrence of same Phone Number Set phNum = .FindNext(phNum) Loop While Not phNum Is Nothing And phNum.Address <> firstAddress End If End If Next End With Dim last_srcRw As Long, srcRw As Long Dim firstAddress As String Dim phNum As Range 'Set Feature_Report!Q:Q as search range With Sheets("Feature Report").Range("Q:Q") 'Loop through RPA!E5:E3004 For srcRw = 5 To 3004 'Make sure the cell is not empty If Sheets("RPA").Range("E" & srcRw) <> "" Then 'Find Phone Number in Feature Report!Q:Q Set phNum = .Find(Sheets("RPA").Range("E" & srcRw)) 'If match is found, check value in Column AI against list If Not phNum Is Nothing Then firstAddress = phNum.Address Do Select Case Sheets("Feature Report").Range("AI" & phNum.Row) Case EPUG 'If value from list is found, copy value to RPA!AO Sheets("RPA").Range("AO" & srcRw) = _ Sheets("Feature Report").Range("AM" & phNum.Row) Exit Do End Select 'If value from list not found, seach for next ccurrence of same Phone Number Set phNum = .FindNext(phNum) Loop While Not phNum Is Nothing And phNum.Address <> firstAddress End If End If Next End With End Sub```Something like this for instance. I know that it doesn't work but it is an example.

Report •

#36
September 23, 2015 at 13:57:33
 I still don't know what you are trying to do. In Response # 26 you said something about Column AI and MSD*. Now you've posted code that looks like you are searching Column AI for EPUG.``` Select Case Sheets("Feature Report").Range("AI" & phNum.Row) Case EPUG```It also looks like you are still copying the value from Feature Report!AM into RPA!AO```'If value from list is found, copy value to RPA!AO Sheets("RPA").Range("AO" & srcRw) = _ Sheets("Feature Report").Range("AM" & phNum.Row)```I'm totally lost.

Report •

#37
September 23, 2015 at 14:09:04
 Correct, You don't seem lost to me. Basically, if EPUG* is in AI paste the value from AM that is next to it into RPA!AO. Still searching the same phone number variables.So everything is the same pretty much, except this time were adding the extra look up of the AI.(MSD* EPUG* it just needs to search for some word within that list of almost impossible to crack Soc codes.) Those codes tell us what feature is on the account then the price. But we only care about certain features/costs.I hope this clarified some stuff. You're not lost.

Report •

#38
September 23, 2015 at 14:35:27
 Can an AI value (MSD*, EPUG*, etc) exist for the same phone number as an AM value (65, 64.99, 40) or is it only one or the other?If it's possible that a given phone number can contain one or the other or even both, just use another Case statement for column AI. I don't see why you need to loop through all of the phone numbers twice, once for AM and once for AI.If you do, then there is still something that I am misunderstanding.

Report •

#39
September 23, 2015 at 14:53:36
 The numbers were for the plan think Data plan. The MSD, EPUG*, is a feature. So yes the line will always have one of those numbers, but may also contain multiple features. Once I figure out how this code works for say EPUG* then I can replicate it for all of the features. For instance.Lets say we pulled a line555-555-5555 We found out it has a \$40 data plan.We also want to know what features it has.So we search for EPUG, it does have EPUG so we want to know how much it costs.Then we search MSD*, It does not have MSD.They are different codes. One finds an already known number.The other finds the soc code containing a random set of letters (EPUG*) and gives us the Value.That is why we have to run it multiple times. Really I should have asked for this one in the first place but I felt like the other one was a simpler request.

Report •

#40
September 23, 2015 at 18:50:19
 I can't do anything until tomorrow night.

Report •

#41
September 24, 2015 at 03:37:37
 I don't know if you are using any debugging techniques to help you figure out what your code is doing, but you should be. Take a look at this tutorial...http://www.computing.net/howtos/sho...message edited by DerbyDad03

Report •

#42
September 24, 2015 at 09:30:13
 I saw something like this yesterday. I learned the f8 to get the single line code to run, which was a game changer, I was running a code yesterday for hours.Code Checklist:X Find last line add 5 delete rest.X Find Data Plan Make a baseline to find Soc code Feature Report AI(EPUG*) and add up all pricing associated with code (Feature Report AO) place it in RPA! Column W for phone number in RPA!E Lined up with Feature Report Q. (Sorry if this is confusing. Trying to be precise)X Import information into the macro.So it looks like that is the last one that I need and I can complete the project.

Report •

#43
September 24, 2015 at 16:08:13
 Okay, so I ran into a problem with ```Option Explicit Sub MobileShare() Dim last_srcRw As Long, srcRw As Long Dim firstAddress As String Dim phNum As Range 'Set Smart_Report!Q:Q as search range With Sheets("Smart Report").Range("Q:Q") 'Loop through RPA!D5:D3004 For srcRw = 5 To 3004 'Make sure the cell is not empty If Sheets("RPA").Range("D" & srcRw) <> "" Then 'Find Phone Number in Feature Report!Q:Q Set phNum = .Find(Sheets("RPA").Range("D" & srcRw)) 'If match is found, check value in Column X against list If Not phNum Is Nothing Then firstAddress = phNum.Address Do Select Case Sheets("Smart Report").Range("V" & phNum.Row) & Sheets("Smart Report").Range("X" & phNum.Row) Case "MOBILE SHARE DEVICE" & "Y" 'If value from list is found, copy value to RPA!S Sheets("RPA").Range("R" & srcRw) = _ Sheets("Smart Report").Range("Z" & phNum.Row) Sheets("RPA").Range("S" & srcRw) = _ Sheets("Smart Report").Range("AA" & phNum.Row) Exit Do End Select 'If value from list not found, seach for next ccurrence of same Phone Number Set phNum = .FindNext(phNum) Loop While Not phNum Is Nothing And phNum.Address <> firstAddress End If End If Next End With Sheets("Proposal").Select End Sub ```Someone recommended using The following as the Case from the original formula doesn't allow wild cards.```Sub Test() Dim i As Integer Dim xRow As Integer With Sheets("Sheet1") xRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With For i = 1 To xRow If Sheets("Sheet1").Cells(i, 1).Value Like "*EPTT*" Then Sheets("Sheet1").Cells(i, 2).Value = "True" Else Sheets("Sheet1").Cells(i, 2).Value = "False" End If Next i End Sub ```The big thing is that we have soc codes such as *EPTT*, *EPUG*, *MSD*. Each soc code can vary based on the product. The formula you provided has worked perfectly for everything I have revised, EXCEPT the soc codes that vary such as EPTT05, EPTT05FMS, EPTT05blah.Again if you have any questions I will help as much as I can. Key Variables.RPA!D5 = Phone NumberFeature Report!Q2 = Phone numbers (List)Feature Report!AI2 = SOC CodeFeature Report!AM2 = MRC (\$) (Add up all instances and put them in RPA!AA5)RPA!AA5 = Where I need the MRC to go once foundAgain thanks for the help thus far. I really appreciate it, I am good at figuring stuff out. Not without a baseline though.

Report •

#44
September 25, 2015 at 03:39:12
 I'm away for the weekend and don't have access to any systems where I can work on this.

Report •

#45
September 25, 2015 at 08:55:12
 Even though I can't work on this right now, I'd want to make another "help forum" related suggestion.In # 43 you said: "The formula you provided has worked perfectly for everything I have revised, EXCEPT the soc codes that vary such as EPTT05, EPTT05FMS, EPTT05blah."First, I don't recall providing any formula. I have offered different versions of macros, but no formulas.Second, if something isn't working, tell us what isn't working. Don't make us guess as to what is wrong or go off testing things, hoping we can figure out what you mean by "it works perfectly except for..."Tell us if you are getting an error, tell us if the data being returned is incorrect/missing/ incomplete, tell us what isn't working.As I've mentioned before, you are totally focused on your project, you know your workbook inside and out. We, on the other hand, are not working on your project full time nor do we have the in depth understanding of the project like you do.Don't make us first figure out what you mean by "it works perfectly except for..." and then move on to solving the problem. Tell us up front what to expect as far as the "not working" part so we know what we are trying to fix.

Report •

#46
September 25, 2015 at 09:47:18
 ```'Set Smart_Report!Q:Q as search range With Sheets("Smart Report").Range("Q:Q") 'Loop through RPA!D5:D3004 For srcRw = 5 To 3004 'Make sure the cell is not empty If Sheets("RPA").Range("D" & srcRw) <> "" Then 'Find Phone Number in Feature Report!Q:Q Set phNum = .Find(Sheets("RPA").Range("D" & srcRw)) ```Works great.``` Option Explicit Sub MobileShare() Dim last_srcRw As Long, srcRw As Long Dim firstAddress As String Dim phNum As Range 'Set Smart_Report!Q:Q as search range With Sheets("Smart Report").Range("Q:Q") 'Loop through RPA!D5:D3004 For srcRw = 5 To 3004 'Make sure the cell is not empty If Sheets("RPA").Range("D" & srcRw) <> "" Then 'Find Phone Number in Feature Report!Q:Q Set phNum = .Find(Sheets("RPA").Range("D" & srcRw)) 'If match is found, check value in Column X against list If Not phNum Is Nothing Then firstAddress = phNum.Address Do Select Case Sheets("Smart Report").Range("V" & phNum.Row) & Sheets("Smart Report").Range("X" & phNum.Row) Case "MOBILE SHARE DEVICE" & "Y" 'If value from list is found, copy value to RPA!S Sheets("RPA").Range("R" & srcRw) = _ Sheets("Smart Report").Range("Z" & phNum.Row) Sheets("RPA").Range("S" & srcRw) = _ Sheets("Smart Report").Range("AA" & phNum.Row) Exit Do End Select 'If value from list not found, seach for next ccurrence of same Phone Number Set phNum = .FindNext(phNum) Loop While Not phNum Is Nothing And phNum.Address <> firstAddress End If End If Next End With Sheets("Proposal").Select End Sub ```Okay, so I ran into a problem with the italicized part because it doesn't allow wild cards. So in instances where I know the Soc Code (MOBILE SHARE DEVICE) it works perfect but in stances where I want it to search EPTT* it doesn't search for EPTT05,EPTT05FMS, etc.```Sub Test() Dim i As Integer Dim xRow As Integer With Sheets("Sheet1") xRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With For i = 1 To xRow If Sheets("Sheet1").Cells(i, 1).Value Like "*EPTT*" Then Sheets("Sheet1").Cells(i, 2).Value = "True" Else Sheets("Sheet1").Cells(i, 2).Value = "False" End If Next i End Sub ```I was told this works. But I don't know how to implement it instead of the Case selection that you provided. Also for 'Loop through RPA!D5:D3004 For srcRw = 5 To 3004Is there a way to write it as from row 5 to last used row in column E?

Report •

#47
September 28, 2015 at 09:15:01
 From the research I have done the case statement doesn't allow for wildcards.

Report •

#48
September 28, 2015 at 20:09:02
 I just downloaded a copy of your workbook from the google drive site. I am still getting an error at this line when I use Ctrl-Shift-Q:` Range("Table5[Office @ Hand]").Select`Please either fix whatever is causing that error or delete the instruction. I don't like working on workbooks that produce errors before I can even start working on the problem I am trying to solve. Once Ctrl-Shift-Q runs without any errors, upload a new copy and I'll take a look at it.

Report •

#49
September 29, 2015 at 10:04:24
 https://drive.google.com/file/d/0B9...Control Shift R.This will import all of the information.The macros I am building are called:Business Messaging Step 2They work, but run Very, Very slowly. Any Ideas are appreciated.

Report •

#50
September 29, 2015 at 17:31:54
 You can use wildcards with Select Case, you just have to dig really deep into how Select Case works.Keep in mind that when you are compare string expressions, the result is either True or False. Therefore, a straight forward Select Case works fine:``` Select Case Range("A1") Case "EPTT" 'Do what you want when A1 contains exactly "EPPT" ' End Select```In essence, Select Case is evaluating A1 = "EPPT" which, just like when used in an Excel cell, returns either True or False.However, if you try to use wildcards, Select Case compares A1 to "*EPPT*" (A1 = "*EPPT*") which is False.So, we need a way to have Select Case return True when A1 is Like "*EPPT*"``` Select Case True Case Range("A1") Like "*EPTT*" 'Do what you want when A1 is Like "*EPPT*" ' End Select```Now, instead of comparing A1 to "EPPT", the code is comparing True to A1 Like "*EPPT*" When A1 contains EPPT anywhere in the cell, the Like returns True. Since True = True, the Select Case returns True.

Report •

#51
September 30, 2015 at 09:14:07
 Oh. That's Nifty. The issue I had with it, was when I wrote that code it was only showing the EPTT for one line, I believe the last line that it was showing.

Report •

#52
September 30, 2015 at 09:41:40
 ``` Sheets("Smart Report").Select Range("AB2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Macro Settings").Select Range("U2").Select ActiveSheet.Paste Range("V2").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Sheets("RPA").Select Range("I5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ```When it gets to the paste Value portion it runs extremely slow.. is there a better way to do this?

Report •

#53
September 30, 2015 at 10:46:59
 Well, for one thing, there is no need to use Select. Rarely do you need to Select an object with VBA in order to perform an action on it.The use of Select can really slow code down.I haven't tested it, but I believe that this snippet of code can replace all of the code you posted in # 52. In any case, that is the format that you should use if you want your code to be more efficient.```Sheets("Smart Report").Range("AB2").End(xlDown).Copy _ Sheets("Macro Settings").Range("U2") Sheets("Macro Settings").Range("V2").End(xlDown).Copy Sheets("RPA").Range("I5").PasteSpecial Paste:=xlPasteValues```message edited by DerbyDad03

Report •

#54
September 30, 2015 at 12:34:13
 Cool Thanks, I got that now :)Last thing. It is looking good btw and I appreciate the help.I have a formula in A2 - U2 on Sheet (Macro settings)I want to copy that formula down but I can't figure that out.Ive tried importing network into tab U2 and hitting End(xlDown).EntireRow.But it doesn't work for some reason.Basically Highlight Row 2, go to U2.End(xlDown).EntireRowPaste. This should give the desired result but I don't know how to write it so that it functions.

Report •

#55
September 30, 2015 at 13:35:33
 https://drive.google.com/file/d/0B9...I saved the copy here for you.I highlighted the row with the columns and left an example.Column U is pasted from another sheet so it will give you however many lines said report has and is needed for the codes in column V anyways.Basically I want all the codes from A2:T2 to fill the formula down to the last row used by Column U.Let me know if there's questions, but it seems pretty straight forward.

Report •

#56
September 30, 2015 at 13:36:04
 re: "Ive tried importing network into tab U2 and hitting End(xlDown).EntireRow."I don't any clue what you mean by that."importing network into tab U2" Huh?Is this what you want?```'Copy Row 2 to Row 3 through last data in Column U With Sheets("Macro Settings") .Rows("2:2").Copy .Rows("3:" & Range("U2").End(xlDown).Row).PasteSpecial End With```

Report •

#57
September 30, 2015 at 14:22:45
 YES! You're The best!

Report •

#58
September 30, 2015 at 15:32:53
 ```Sub Import() Application.ScreenUpdating = False 'Import basic stuff 'Network Sheet8.Range("U2:U3001") = Sheet3.Range("AB2:AB3001").Value 'Network Addon Sheet2.Range("I5:I3004") = Sheet8.Range("V2:V3001").Value 'FAN Sheet2.Range("B5:B3004") = Sheet3.Range("A2:A3001").Value 'BAN Sheet2.Range("C5:C3004") = Sheet3.Range("E2:E3001").Value 'Phone Number Sheet2.Range("D5:D3004") = Sheet3.Range("Q2:Q3001").Value 'End Date Sheet2.Range("E5:E3004") = Sheet3.Range("I2:I3001").Value 'Make Sheet2.Range("F5:F3004") = Sheet3.Range("AC2:AC3001").Value 'Model Sheet2.Range("G5:G3004") = Sheet3.Range("AD2:AD3001").Value 'User Sheet2.Range("H5:H3004") = Sheet3.Range("P2:P3001").Value 'Primary Line Sheet2.Range("Q5:Q3004") = Sheet3.Range("X2:X3001").Value 'Minutes Sheet2.Range("AQ5:AQ3004") = Sheet3.Range("AK2:AK3001").Value 'Data Sheet2.Range("AR5:AR3004") = Sheet3.Range("AL2:AL3001").Value 'DragFormulas Paste 'Copy Row 2 to Row 3 through last data in Column U With Sheets("Macro Settings") .Rows("2:2").Copy .Rows("3:" & Range("U2").End(xlDown).Row).PasteSpecial End With 'International Sheet2.Range("X5:X3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("AZ5:AZ3004") = Sheet8.Range("A2:A3001").Value 'Insuarance Sheet2.Range("Y5:Y3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BA5:BA3004") = Sheet8.Range("A2:A3001").Value 'Enhanced Push to Talk Sheet2.Range("AA5:AA3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BC5:BC3004") = Sheet8.Range("A2:A3001").Value 'AT&T MDM Sheet2.Range("AB5:AB3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BDZ5:BD3004") = Sheet8.Range("A2:A3001").Value 'Forms Sheet2.Range("AC5:AC3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BE5:BE3004") = Sheet8.Range("A2:A3001").Value 'Toggle Sheet2.Range("AD5:AD3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BF5:BF3004") = Sheet8.Range("A2:A3001").Value 'MRAS Sheet2.Range("AE5:AE3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BG5:BG3004") = Sheet8.Range("A2:A3001").Value 'Comet Sheet2.Range("AF5:AF3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BH5:BH3004") = Sheet8.Range("A2:A3001").Value 'Airwatch Sheet2.Range("AI5:AI3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BK5:BK3004") = Sheet8.Range("A2:A3001").Value 'AprivaPay Sheet2.Range("AG5:AG3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BI5:BI3004") = Sheet8.Range("A2:A3001").Value 'Big Tin Can Sheet2.Range("AL5:AL3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BN5:BN3004") = Sheet8.Range("A2:A3001").Value 'Box Sheet2.Range("AM5:AM3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BO5:BO3004") = Sheet8.Range("A2:A3001").Value 'OAH Sheet2.Range("AN5:AN3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BP5:BP3004") = Sheet8.Range("A2:A3001").Value 'Office Direct Sheet2.Range("AO5:AO3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BQ5:BQ3004") = Sheet8.Range("A2:A3001").Value 'Access My LAN Sheet2.Range("Z5:Z3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BB5:BB3004") = Sheet8.Range("A2:A3001").Value 'Business Messaging Sheet2.Range("AP5:AP3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BR5:BR3004") = Sheet8.Range("A2:A3001").Value 'Xora Sheet2.Range("AK5:AK3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BM5:BM3004") = Sheet8.Range("A2:A3001").Value 'TeleNAV Sheet2.Range("AJ5:AJ3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("BL5:BL3004") = Sheet8.Range("A2:A3001").Value 'Unlimited Data Sheet2.Range("X5:X3004") = Sheet8.Range("A2:A3001").Value Sheet2.Range("AZ5:AZ3004") = Sheet8.Range("A2:A3001").Value Application.ScreenUpdating = True End Sub```Sooo this can't be the best way to do this, cause it takes like 30 minutes to do 10 lines :/ Any suggestions?

Report •

#59
October 1, 2015 at 09:05:51
 Nevermind. I got it.!

Report •

#60
October 1, 2015 at 09:26:41
 Good. Are we done? There have been so many questions and versions over the life of this thread that I don't know where we stand.

Report •

#61
October 5, 2015 at 11:58:46
 Yes, I figured it out for the most part. Just making sure a few scripts are correct.

Report •