Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then Help is greatly appreciated!
If then statements have for some reason left me dumbfounded!
This is what I need to do in VBA: Look in Cell A1 for "*Rep Summary", if it exists look in cell D10 for the reps name and give me that name in cell q10. If "* Rep Summary", does not exist then return nothing. Lastly; repeat this down the column Q:Q, until there is no data left in Column A:A. Thanks for your help...this one as I stated before as left me with a definite "DUUUHHHH!" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then Help is greatly appreciated!
This formula goes in Q10:
=if(a1="*Rep Summary",d10,"") Watch out. Once you used "*Rep Summary" and the other time "* Rep Summary". If a1 can contain other stuff along with "Rep Summary", you could use: =if(countif(a1,"*rep summary*")0,d10,"") (the asterisks are wild cards in that last formula.) And drag it down as far as you need. Debra Dalgleish has instructions with pictures: http://contextures.com/xlDataEntry01.html#Mouse " wrote: If then statements have for some reason left me dumbfounded! This is what I need to do in VBA: Look in Cell A1 for "*Rep Summary", if it exists look in cell D10 for the reps name and give me that name in cell q10. If "* Rep Summary", does not exist then return nothing. Lastly; repeat this down the column Q:Q, until there is no data left in Column A:A. Thanks for your help...this one as I stated before as left me with a definite "DUUUHHHH!" -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then Help is greatly appreciated!
Dave,
Thanks for the help...but how would I go about placing this in VBA? Currently I do this via formulation and when I send out I copy paste into another workbook. The reason I want to convert to VBA is that I can send out the actual workbook and stop the double work. As for your "Watch Out"; I didn't state it plainly. If "* Rep Summary" does not exist in cell A1, do nothing. Does that help in explaining what I need it to do? Thanks, Hans |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then Help is greatly appreciated!
This formula:
=if(a1="*Rep Summary",d10,"") Seems kind of strange to copy down a range. In most cases I've seen, when I want to copy down a range, the formula refers to cells on the same row--not a cell 9 rows down. But you can do this kind of thing: Option Explicit sub testme() dim LastRow as long with worksheets("Sheet99") lastrow = .cells(.rows.count,"A").end(xlup).row .range("q1:Q" & lastrow).formula _ = "=if(a1=""* Rep Summary"",d10,"""")" end with end sub But I ended up with formulas in Q1:Qxxx that looked like: =IF(A1="* Rep Summary",D10,"") =IF(A2="* Rep Summary",D11,"") =IF(A3="* Rep Summary",D12,"") =IF(A4="* Rep Summary",D13,"") =IF(A5="* Rep Summary",D14,"") =IF(A6="* Rep Summary",D15,"") =IF(A7="* Rep Summary",D16,"") =IF(A8="* Rep Summary",D17,"") =IF(A9="* Rep Summary",D18,"") =IF(A10="* Rep Summary",D19,"") And that just looks pretty weird to me. " wrote: Dave, Thanks for the help...but how would I go about placing this in VBA? Currently I do this via formulation and when I send out I copy paste into another workbook. The reason I want to convert to VBA is that I can send out the actual workbook and stop the double work. As for your "Watch Out"; I didn't state it plainly. If "* Rep Summary" does not exist in cell A1, do nothing. Does that help in explaining what I need it to do? Thanks, Hans -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then Help is greatly appreciated!
Dave,
Your right, it is weird...I had a typo. It should have read =if(a1="* Rep Summary",d1,""). I have not tried your solution yet. If I change the typo, will your solution work or is there another change that I need to make? Thanks for everything Hans |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then Help is greatly appreciated!
You'll have to fix the formula in the code.
But you should save your work before you try it. Then if it doesn't work, you can close without saving. " wrote: Dave, Your right, it is weird...I had a typo. It should have read =if(a1="* Rep Summary",d1,""). I have not tried your solution yet. If I change the typo, will your solution work or is there another change that I need to make? Thanks for everything Hans -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then Help is greatly appreciated!
Dave,
Sorry for the double reply.... This is what I have done and it works to Perfection! THANKS!!!! 'Adds Rep Name in Last Column of Call Summary Dim LastRow As Long With Worksheets("Call Summary") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("q1:Q" & LastRow).Formula _ = "=if(a1=""* Rep Summary"",d1,"""")" This I added at the end of your code... Worksheets("Call Summary").Select Range("q1:Q" & LastRow).Select Dim Cell As Range For Each Cell In Selection Cell.Value = Cell.Value Next End With I now have 2 questions: 1) (.Rows.Count, "A")....what does the "A" stand for or mean?? I am very new to VBA...about a month or so...and I understand what most of your code is doing, but this one I do not. 2) In the macro where this is assigned; Multiple calculations, retrieving data etc... are being done...Your code is much cleaner than what I was utilizing before so I copied it again and made some changes. The next section of code is this: 'Gets Time in Stores With Worksheets("Summary") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("D40:D" & LastRow).Formula _ = "=IF(ISERROR(INDEX('Rep Performance Analysis'!G:G,MATCH(Summary!C40,'Rep Performance Analysis'!B:B,0))),"""",INDEX('Rep Performance Analysis'!G:G,MATCH(Summary!C40,'Rep Performance Analysis'!B:B,0)))" End With What I would like to do is add this at the end: Worksheets("Summary").Select Range("D40:D" & LastRow).Select Dim Cell As Range For Each Cell In Selection Cell.Value = Cell.Value Next But, as you probably already know this gives me an error; "Duplicate error in current scope" I will need to do the "value only" part several times and each time it refers to a different section of the workbook,sheets etc... Would I be better off waiting to do this until the very end of the code or do something different? I hope this makes sense.... Thanks Hans |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then Help is greatly appreciated!
First, you may want to try it this way:
Option Explicit sub testme() dim LastRow as long with worksheets("Call Summary") lastrow = .cells(.rows.count,"A").end(xlup).row with .range("q1:Q" & lastrow) .formula = "=if(a1=""* Rep Summary"",d1,"""")" .value = .value end with end with end sub That way, you convert all the cells in that range at one time--instead of looping through the cells. #1. In this code: with worksheets("Call Summary") lastrow = .cells(.rows.count,"A").end(xlup).row The objects with dots in front of them (.cells and .rows) refer to the object that was used in the previous With statement. In this case, worksheets("call summary"). And cells() has two pieces. The first is the row and the second is the column. Since there are 65536 rows in that worksheet (and any worksheet), this line: lastrow = .cells(.rows.count,"A").end(xlup).row is equivalent to: lastrow = .cells(65536,"A").end(xlup).row which could be written as: lastrow = .range("a65536").end(xlup).row This is the same thing as selecting A65536 and hitting the End key, then the up arrow. You'll end up in the last cell in column A that was used. I like to use .rows.count, since different versions of excel have different number of rows (xl95 had 16k, xl97-xl2003 had 64k, and the new version will have a meg of rows). The code won't have to change like: .range("a65536") would have to. ========== And you'd only have to "Dim Cell As Range" one time (usually near the top of the procedure for most people). So you could just drop that second "dim cell as range" line. But even better would be to get it all at once--like the sample above. ...... So don't scroll down until you've tried your modification. Did it look like this? .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. Option Explicit Sub testme() Dim LastRow As Long With Worksheets("Call Summary") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("q1:Q" & LastRow) .Formula = "=if(a1=""* Rep Summary"",d1,"""")" .Value = .Value End With With .Range("D40:D" & LastRow) .Formula _ = "=IF(ISERROR(INDEX('Rep Performance Analysis'!G:G," & _ "MATCH(Summary!C40,'Rep Performance Analysis'!B:B,0))),""""," _ & "INDEX('Rep Performance Analysis'!G:G," _ & "MATCH(Summary!C40,'Rep Performance Analysis'!B:B,0)))" .Value = .Value End With End With End Sub ====== I use Testme() as names of subroutines--change that to something meaningful. ps. You put this stuff in "Call Summary", but in the second formula, you pointed at "Summary". Was that on purpose? " wrote: Dave, Sorry for the double reply.... This is what I have done and it works to Perfection! THANKS!!!! 'Adds Rep Name in Last Column of Call Summary Dim LastRow As Long With Worksheets("Call Summary") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("q1:Q" & LastRow).Formula _ = "=if(a1=""* Rep Summary"",d1,"""")" This I added at the end of your code... Worksheets("Call Summary").Select Range("q1:Q" & LastRow).Select Dim Cell As Range For Each Cell In Selection Cell.Value = Cell.Value Next End With I now have 2 questions: 1) (.Rows.Count, "A")....what does the "A" stand for or mean?? I am very new to VBA...about a month or so...and I understand what most of your code is doing, but this one I do not. 2) In the macro where this is assigned; Multiple calculations, retrieving data etc... are being done...Your code is much cleaner than what I was utilizing before so I copied it again and made some changes. The next section of code is this: 'Gets Time in Stores With Worksheets("Summary") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("D40:D" & LastRow).Formula _ = "=IF(ISERROR(INDEX('Rep Performance Analysis'!G:G,MATCH(Summary!C40,'Rep Performance Analysis'!B:B,0))),"""",INDEX('Rep Performance Analysis'!G:G,MATCH(Summary!C40,'Rep Performance Analysis'!B:B,0)))" End With What I would like to do is add this at the end: Worksheets("Summary").Select Range("D40:D" & LastRow).Select Dim Cell As Range For Each Cell In Selection Cell.Value = Cell.Value Next But, as you probably already know this gives me an error; "Duplicate error in current scope" I will need to do the "value only" part several times and each time it refers to a different section of the workbook,sheets etc... Would I be better off waiting to do this until the very end of the code or do something different? I hope this makes sense.... Thanks Hans -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then Help is greatly appreciated!
Dave,
In the workbook I have 3 sheets (call summary, rep performance, PTO) that I reference back and place in the summary sheet, the 4th sheet ( AND the Main Reporting sheet). That was the difference in regards to your ps. However; with that being said, you have given me enough information that I was able to change the subroutines and make them work in each case. At this point, I have not finished all the conversions from formulas in the cells to "VBA based formulas" and have reduced the file size from 6-7 megs to 966 KB which is one of the things I wanted to achieve, easier to email. The other was previously I had to copy the workbook into another and paste just the values, which was a pain to do! And then email.... I believe that it is all a good thing at this point... The last thing I would like to do is be able to allow the field managers to utilize this workbook on their own... But, the opening lines in my code is: Workbooks.Open Filename:= _ "C:\Documents and Settings\Administrator\My Documents\ISS RAW DATA\Time\Call Summary.xls" Windows("Call Summary.xls").Activate Workbooks("Call Summary.xls").Sheets("Call Summary").Range("A1:T4000").Copy _ Workbooks("Conner Time Compliance.xls").Sheets("Call Summary").Range("A1") Workbooks.Open Filename:= _ "C:\Documents and Settings\Administrator\My Documents\ISS RAW DATA\Time\Rep Performance Analysis.xls" Windows("Rep Performance Analysis.xls").Activate Workbooks("Rep Performance Analysis.xls").Sheets("Rep Performance Analysis").Range("a1:T4000").Copy _ Workbooks("Conner Time Compliance.xls").Sheets("Rep Performance Analysis").Range("A1") Workbooks("Call Summary.xls").Close Workbooks("Rep Performance Analysis.xls").Close What I would like is for the field managers to be able to save this workbook in a specified folder along with "Call Summary" and "Rep Performance Analysis" then be able to run this... My question now is, How would I write it so that the "Summary Sheet" knows which folder to in for the other sheets? i.e. what if a manager saves these files in c:\My Documents\My Reports. The code would not read,"Workbooks.Open Filename:= c:\MyDocuments\MyReports.... So how would I make this happen? And Thanks for the explanation earlier...that helped ALOT in understanding how things happen! Hans |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then Help is greatly appreciated!
If they stored all the files in one folder, then as soon as one of those
workbooks is open, then you can use the folder that holds that open file. It kind of looks like "Conner Time Compliance.xls" is the workbook that owns the code. If that's true, then you can use ThisWorkbook instead of using a variable. Option Explicit Sub testme() Dim CallSummWkbk As Workbook Dim RepPerfWkbk As Workbook Dim ConnerTimeWkbk As Workbook 'may not be needed??? Set CallSummWkbk = Workbooks.Open _ (Filename:=ThisWorkbook.Path & "\" & "Call Summary.xls") Set RepPerfWkbk = Workbooks.Open _ (filenameThisWorkbook.Path & "\" & "Rep Performance Analysis.xls") 'do you need conner time compliance to be opened? Set ConnerTimeWkbk = Workbooks.Open _ (filenameThisWorkbook.Path & "\" & "Conner Time Compliance.xls") 'or is it the workbook that owns the code that's running? 'so we could just use ThisWorkbook 'which one should be used? CallSummWkbk.Worksheets("call summary").Range("a1:t4000").Copy _ Destination:=ConnerTimeWkbk.Worksheets("call summary").Range("a1") 'or CallSummWkbk.Worksheets("call summary").Range("a1:t4000").Copy _ Destination:=ThisWorkbook.Worksheets("call summary").Range("a1") 'same question here... RepPerfWkbk.Worksheets("rep performance analaysis").Range("a1:T4000").Copy _ Destination:=ConnerTimeWkbk _ .Worksheets("rep performance analaysis").Range("a1") 'or RepPerfWkbk.Worksheets("rep performance analaysis").Range("a1:T4000").Copy _ Destination:=ThisWorkbook _ .Worksheets("rep performance analaysis").Range("a1") CallSummWkbk.Close savechanges:=False RepPerfWkbk.Close savechanges:=False End Sub ======= I don't think you can force anyone to anything you want them to do (well, it never works 100% for me!). But I'd tell, er, ask the users to store all the files in a dedictated folder--just give it a nice unique name: c:\SummaryAnalysisCompliance Then you could hardcode the folder using that name. You could use dir() to test to see if the files exist and give them a message if they don't. Dim testStr as string teststr = "" on error resume next teststr = dir("c:\SummaryAnalysisCompliance\Call Summary.xls" on error goto 0 if teststr = "" then msgbox "input file missing." & _ "And give them a nice message about what they should do" exit sub end if ====== If you can't win that, you could always use: application.getopenfilename to let them point to the file of their choice. ===== I think I'd try to get them all to use that dedicated folder. It'll make helping them (over the phone??) getting things set up. And by making it consistent, users could help each other. " wrote: Dave, In the workbook I have 3 sheets (call summary, rep performance, PTO) that I reference back and place in the summary sheet, the 4th sheet ( AND the Main Reporting sheet). That was the difference in regards to your ps. However; with that being said, you have given me enough information that I was able to change the subroutines and make them work in each case. At this point, I have not finished all the conversions from formulas in the cells to "VBA based formulas" and have reduced the file size from 6-7 megs to 966 KB which is one of the things I wanted to achieve, easier to email. The other was previously I had to copy the workbook into another and paste just the values, which was a pain to do! And then email.... I believe that it is all a good thing at this point... The last thing I would like to do is be able to allow the field managers to utilize this workbook on their own... But, the opening lines in my code is: Workbooks.Open Filename:= _ "C:\Documents and Settings\Administrator\My Documents\ISS RAW DATA\Time\Call Summary.xls" Windows("Call Summary.xls").Activate Workbooks("Call Summary.xls").Sheets("Call Summary").Range("A1:T4000").Copy _ Workbooks("Conner Time Compliance.xls").Sheets("Call Summary").Range("A1") Workbooks.Open Filename:= _ "C:\Documents and Settings\Administrator\My Documents\ISS RAW DATA\Time\Rep Performance Analysis.xls" Windows("Rep Performance Analysis.xls").Activate Workbooks("Rep Performance Analysis.xls").Sheets("Rep Performance Analysis").Range("a1:T4000").Copy _ Workbooks("Conner Time Compliance.xls").Sheets("Rep Performance Analysis").Range("A1") Workbooks("Call Summary.xls").Close Workbooks("Rep Performance Analysis.xls").Close What I would like is for the field managers to be able to save this workbook in a specified folder along with "Call Summary" and "Rep Performance Analysis" then be able to run this... My question now is, How would I write it so that the "Summary Sheet" knows which folder to in for the other sheets? i.e. what if a manager saves these files in c:\My Documents\My Reports. The code would not read,"Workbooks.Open Filename:= c:\MyDocuments\MyReports.... So how would I make this happen? And Thanks for the explanation earlier...that helped ALOT in understanding how things happen! Hans -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then Help is greatly appreciated!
OK Dave....
Thought I would give you the update, since your help was immeasurable...The time report went from 6-7 megs down to around 1meg. It use to take me 10 minutes or so to complete and now it is down to about 15 seconds. I works like a CHARM! A few more tweeks and that project will be finished. Now, I am on to another challenge.... The 3 things at this point I am working on is: 1) How to have the code to look in a folder and open/activate each workbook without me explicitly calling each workbook by name. When I pull reports I place them in a designated folder and name each one 1,2,3 etc...One month I could have 10 in the folder and next month 30. So, it varies considerably. I currently am using this line: Workbooks.Open Filename:= _ "C:\Documents and Settings\Administrator\My Documents\ISS RAW DATA\Current Special Project\s3.htm" So, you see I would have to add additional statements each time the workbooks increase...not very efficient. 2) I looked back at the code on LastRow to get some ideas, but hmmm... What I do is select a cell in the report, say file S3.htm, cell A10 and use that as a header in another summary workbook, for example, Summary, cell H1. What I need to have is the code then to open a new file S4.htm find the cell that I select as the header and place that into Summary cell I1 and then keep doing that until it cannot find anymore workbooks in the folder. 3)Last I know that index match does not do very well when you have more than one potential match...it will only return the 1st one it comes to. In my situation that may not be the answer that I am looking for...I have tried index,match match but always get an error message. To utilize this do you have to setup range names, tables or something else? Thanks again, Hans |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then Help is greatly appreciated!
#1. I use this kind of stuff for my shell to open all the excel files in a
single folder: You can fiddle with it to open your .htm files. Option Explicit Sub testme01() Dim myNames() As String Dim fCtr As Long Dim myFile As String Dim myPath As String Dim DestCell As Range Dim wkbk As Workbook Dim wks As Worksheet 'change to point at the folder to check myPath = "c:\my documents\excel" If Right(myPath, 1) < "\" Then myPath = myPath & "\" End If myFile = "" On Error Resume Next myFile = Dir(myPath & "*.xls") On Error GoTo 0 If myFile = "" Then MsgBox "no files found" Exit Sub End If Application.ScreenUpdating = False 'get the list of files fCtr = 0 Do While myFile < "" fCtr = fCtr + 1 ReDim Preserve myNames(1 To fCtr) myNames(fCtr) = myFile myFile = Dir() Loop If fCtr 0 Then For fCtr = LBound(myNames) To UBound(myNames) Set wkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr), _ UpdateLinks:=0, ReadOnly:=True) 'do a bunch of stuff with that file. wkbk.Close savechanges:=False Next fCtr End If Application.ScreenUpdating = True End Sub =========================== You can always define a variable to hold your info. Dim A10FromS3 as String and when you open that file, just extract that value: a10froms3 = wkbk.worksheets(1).range("a10").value (but that's just a guess, I don't quite see what you're doing.) ============= Dim Res as variant dim rng as range dim something as string with wkbk.worksheets("sheet1") set rng = .range("a:a") end with something = "whateveryou're looking up" res = application.match(something, rng,0) if iserror(res) then 'no match found else msgbox res end if " wrote: OK Dave.... Thought I would give you the update, since your help was immeasurable...The time report went from 6-7 megs down to around 1meg. It use to take me 10 minutes or so to complete and now it is down to about 15 seconds. I works like a CHARM! A few more tweeks and that project will be finished. Now, I am on to another challenge.... The 3 things at this point I am working on is: 1) How to have the code to look in a folder and open/activate each workbook without me explicitly calling each workbook by name. When I pull reports I place them in a designated folder and name each one 1,2,3 etc...One month I could have 10 in the folder and next month 30. So, it varies considerably. I currently am using this line: Workbooks.Open Filename:= _ "C:\Documents and Settings\Administrator\My Documents\ISS RAW DATA\Current Special Project\s3.htm" So, you see I would have to add additional statements each time the workbooks increase...not very efficient. 2) I looked back at the code on LastRow to get some ideas, but hmmm... What I do is select a cell in the report, say file S3.htm, cell A10 and use that as a header in another summary workbook, for example, Summary, cell H1. What I need to have is the code then to open a new file S4.htm find the cell that I select as the header and place that into Summary cell I1 and then keep doing that until it cannot find anymore workbooks in the folder. 3)Last I know that index match does not do very well when you have more than one potential match...it will only return the 1st one it comes to. In my situation that may not be the answer that I am looking for...I have tried index,match match but always get an error message. To utilize this do you have to setup range names, tables or something else? Thanks again, Hans -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
The MS Graph Tool should be debugged and greatly enhanced. | Charts and Charting in Excel | |||
Can't quite get what I want with bar chart; help appreciated | Charts and Charting in Excel | |||
Using excel to manage event - ANY input deeply appreciated! :-) | Excel Discussion (Misc queries) | |||
Can't get out of selection? Assistance would be most appreciated... | New Users to Excel | |||
Hello, again Help is appreciated | Excel Worksheet Functions |