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 columnAM
CTN = sheet4 columnQ


See More: SUMIF formula to VBA

Report •


#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!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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
Well, first, it doesn't look like you followed my suggestion to read the instructions on how to post example data as requested.

Second, please keep in mind that we can't see your workbook from where we're sitting, nor do know anything about your process. Your example data doesn't help me understand what your formula is doing or what you want the VBA to do.

Assume we know nothing. The more details you provide, the easier it is for us to help you. We want to help, but at least as far as I am concerned, I don't think we have enough information to write any code. Maybe someone else will step up and explain what I am not seeing or perhaps you could provide more information.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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

Feature_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?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#10
September 21, 2015 at 13:07:04
Removed

message 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 RPA

1 - 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_Report

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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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:E3004
Cntrl shift down
Down 5x
then 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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or 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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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:

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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
While you may find it easier to read, you are in a very small minority. Not only does Excel's VBA editor include a measure of customizable, automatic indention, but there are add-ins available to help make code more readable through the use of indentation, etc.

Here is just one of many sites that discuss indentation as a means to make the code more readable. A Google search will bring up many more discussions.

http://www.excelfunctions.net/VBA-C...

Trust me, if you are posting code in help forums and looking for assistance, I am not going to be the only one that requests that you make your code easier to read by including comments and indentation.

Comments are just as important as indentation, not just for the reader, but also for the author. Trust me again: a year from now when you need to modify some of the code you've written (for example, the Step 2 macro) you are going to have a really hard time remembering what you were thinking when you wrote the code.

A wise man once said:

"Code Tells You How, Comments Tell You Why"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 line
555-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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 Number
Feature Report!Q2 = Phone numbers (List)
Feature Report!AI2 = SOC Code
Feature Report!AM2 = MRC ($) (Add up all instances and put them in RPA!AA5)
RPA!AA5 = Where I need the MRC to go once found

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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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
            <i>Select Case Sheets("Smart Report").Range("V" & phNum.Row) & Sheets("Smart Report").Range("X" & phNum.Row)</i>
               <i> Case "MOBILE SHARE DEVICE" & "Y"</i>
            

'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 3004

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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 2

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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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).EntireRow
Paste. 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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 •

Ask Question