software

If then statements have for some reason left me dumbfounded!
This is what I need to do in VBA:
Look in Cell A1 for quot;*Rep Summaryquot;, if it exists look in cell D10 for
the reps name and give me that name in cell q10. If quot;* Rep Summaryquot;,
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 quot;DUUUHHHH!quot;This formula goes in Q10:

=if(a1=quot;*Rep Summaryquot;,d10,quot;quot;)

Watch out. Once you used quot;*Rep Summaryquot; and the other time quot;* Rep Summaryquot;.

If a1 can contain other stuff along with quot;Rep Summaryquot;, you could use:

=if(countif(a1,quot;*rep summary*quot;)gt;0,d10,quot;quot;)

(the asterisks are wild cards in that last formula.)

And drag it down as far as you need.

Debra Dalgleish has instructions with pictures:
contextures.com/xlDataEntry01.html#Mouse

quot; wrote:
gt;
gt; If then statements have for some reason left me dumbfounded!
gt; This is what I need to do in VBA:
gt; Look in Cell A1 for quot;*Rep Summaryquot;, if it exists look in cell D10 for
gt; the reps name and give me that name in cell q10. If quot;* Rep Summaryquot;,
gt; does not exist then return nothing.
gt; Lastly; repeat this down the column Q:Q, until there is no data left in
gt; Column A:A.
gt;
gt; Thanks for your help...this one as I stated before as left me with a
gt; definite quot;DUUUHHHH!quot;

--

Dave Peterson

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 quot;Watch Outquot;; I didn't state it plainly. If quot;* Rep Summaryquot;
does not exist in cell A1, do nothing. Does that help in explaining
what I need it to do?

Thanks,

HansThis formula:

=if(a1=quot;*Rep Summaryquot;,d10,quot;quot;)

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(quot;Sheet99quot;)
lastrow = .cells(.rows.count,quot;Aquot;).end(xlup).row

.range(quot;q1:Qquot; amp; lastrow).formula _
= quot;=if(a1=quot;quot;* Rep Summaryquot;quot;,d10,quot;quot;quot;quot;)quot;
end with
end sub

But I ended up with formulas in Q1:Qxxx that looked like:

=IF(A1=quot;* Rep Summaryquot;,D10,quot;quot;)
=IF(A2=quot;* Rep Summaryquot;,D11,quot;quot;)
=IF(A3=quot;* Rep Summaryquot;,D12,quot;quot;)
=IF(A4=quot;* Rep Summaryquot;,D13,quot;quot;)
=IF(A5=quot;* Rep Summaryquot;,D14,quot;quot;)
=IF(A6=quot;* Rep Summaryquot;,D15,quot;quot;)
=IF(A7=quot;* Rep Summaryquot;,D16,quot;quot;)
=IF(A8=quot;* Rep Summaryquot;,D17,quot;quot;)
=IF(A9=quot;* Rep Summaryquot;,D18,quot;quot;)
=IF(A10=quot;* Rep Summaryquot;,D19,quot;quot;)

And that just looks pretty weird to me.quot; wrote:
gt;
gt; Dave,
gt;
gt; Thanks for the help...but how would I go about placing this in VBA?
gt; Currently I do this via formulation and when I send out I copy paste
gt; into another workbook. The reason I want to convert to VBA is that I
gt; can send out the actual workbook and stop the double work.
gt; As for your quot;Watch Outquot;; I didn't state it plainly. If quot;* Rep Summaryquot;
gt; does not exist in cell A1, do nothing. Does that help in explaining
gt; what I need it to do?
gt;
gt; Thanks,
gt;
gt; Hans

--

Dave Peterson

Dave,
Your right, it is weird...I had a typo. It should have read =if(a1=quot;*
Rep Summaryquot;,d1,quot;quot;).
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

HansYou'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.

quot; wrote:
gt;
gt; Dave,
gt; Your right, it is weird...I had a typo. It should have read =if(a1=quot;*
gt; Rep Summaryquot;,d1,quot;quot;).
gt; I have not tried your solution yet.
gt; If I change the typo, will your solution work or is there another
gt; change that I need to make?
gt;
gt; Thanks for everything
gt;
gt; Hans

--

Dave Peterson

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(quot;Call Summaryquot;)
LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row
.Range(quot;q1:Qquot; amp; LastRow).Formula _
= quot;=if(a1=quot;quot;* Rep Summaryquot;quot;,d1,quot;quot;quot;quot;)quot;

This I added at the end of your code...

Worksheets(quot;Call Summaryquot;).Select
Range(quot;q1:Qquot; amp; 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, quot;Aquot;)....what does the quot;Aquot; 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(quot;Summaryquot;)
LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row
.Range(quot;D40quot; amp; LastRow).Formula _
= quot;=IF(ISERROR(INDEX('Rep Performance
Analysis'!G:G,MATCH(Summary!C40,'Rep Performance
Analysis'!B:B,0))),quot;quot;quot;quot;,INDEX('Rep Performance
Analysis'!G:G,MATCH(Summary!C40,'Rep Performance Analysis'!B:B,0)))quot;End With

What I would like to do is add this at the end:

Worksheets(quot;Summaryquot;).Select
Range(quot;D40quot; amp; 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; quot;Duplicate
error in current scopequot; I will need to do the quot;value onlyquot; 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

HansFirst, you may want to try it this way:

Option Explicit
sub testme()
dim LastRow as long
with worksheets(quot;Call Summaryquot;)
lastrow = .cells(.rows.count,quot;Aquot;).end(xlup).row

with .range(quot;q1:Qquot; amp; lastrow)
.formula = quot;=if(a1=quot;quot;* Rep Summaryquot;quot;,d1,quot;quot;quot;quot;)quot;
.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(quot;Call Summaryquot;)
lastrow = .cells(.rows.count,quot;Aquot;).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(quot;call
summaryquot;).

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,quot;Aquot;).end(xlup).row
is equivalent to:
lastrow = .cells(65536,quot;Aquot;).end(xlup).row
which could be written as:
lastrow = .range(quot;a65536quot;).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(quot;a65536quot;) would
have to.

==========

And you'd only have to quot;Dim Cell As Rangequot; one time (usually near the top of the
procedure for most people). So you could just drop that second quot;dim cell as
rangequot; 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(quot;Call Summaryquot;)
LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row

With .Range(quot;q1:Qquot; amp; LastRow)
.Formula = quot;=if(a1=quot;quot;* Rep Summaryquot;quot;,d1,quot;quot;quot;quot;)quot;
.Value = .Value
End With

With .Range(quot;D40quot; amp; LastRow)
.Formula _
= quot;=IF(ISERROR(INDEX('Rep Performance Analysis'!G:G,quot; amp; _
quot;MATCH(Summary!C40,'Rep Performance Analysis'!B:B,0))),quot;quot;quot;quot;,quot; _
amp; quot;INDEX('Rep Performance Analysis'!G:G,quot; _
amp; quot;MATCH(Summary!C40,'Rep Performance Analysis'!B:B,0)))quot;
.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 quot;Call Summaryquot;, but in the second formula, you
pointed at quot;Summaryquot;. Was that on purpose?quot; wrote:
gt;
gt; Dave,
gt;
gt; Sorry for the double reply....
gt;
gt; This is what I have done and it works to Perfection! THANKS!!!!
gt;
gt; 'Adds Rep Name in Last Column of Call Summary
gt; Dim LastRow As Long
gt; With Worksheets(quot;Call Summaryquot;)
gt; LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row
gt; .Range(quot;q1:Qquot; amp; LastRow).Formula _
gt; = quot;=if(a1=quot;quot;* Rep Summaryquot;quot;,d1,quot;quot;quot;quot;)quot;
gt;
gt; This I added at the end of your code...
gt;
gt; Worksheets(quot;Call Summaryquot;).Select
gt; Range(quot;q1:Qquot; amp; LastRow).Select
gt; Dim Cell As Range
gt; For Each Cell In Selection
gt; Cell.Value = Cell.Value
gt; Next
gt;
gt; End With
gt;
gt; I now have 2 questions:
gt;
gt; 1) (.Rows.Count, quot;Aquot;)....what does the quot;Aquot; stand for or mean?? I am
gt; very new to VBA...about a month or so...and I understand what most of
gt; your code is doing, but this one I do not.
gt;
gt; 2) In the macro where this is assigned; Multiple calculations,
gt; retrieving data etc... are being done...Your code is much cleaner than
gt; what I was utilizing before so I copied it again and made some changes.
gt; The next section of code is this:
gt;
gt; 'Gets Time in Stores
gt; With Worksheets(quot;Summaryquot;)
gt; LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row
gt; .Range(quot;D40quot; amp; LastRow).Formula _
gt; = quot;=IF(ISERROR(INDEX('Rep Performance
gt; Analysis'!G:G,MATCH(Summary!C40,'Rep Performance
gt; Analysis'!B:B,0))),quot;quot;quot;quot;,INDEX('Rep Performance
gt; Analysis'!G:G,MATCH(Summary!C40,'Rep Performance Analysis'!B:B,0)))quot;
gt;
gt; End With
gt;
gt; What I would like to do is add this at the end:
gt;
gt; Worksheets(quot;Summaryquot;).Select
gt; Range(quot;D40quot; amp; LastRow).Select
gt; Dim Cell As Range
gt; For Each Cell In Selection
gt; Cell.Value = Cell.Value
gt; Next
gt;
gt; But, as you probably already know this gives me an error; quot;Duplicate
gt; error in current scopequot; I will need to do the quot;value onlyquot; part several
gt; times and each time it refers to a different section of the
gt; workbook,sheets etc... Would I be better off waiting to do this until
gt; the very end of the code or do something different?
gt; I hope this makes sense....
gt;
gt; Thanks
gt;
gt; Hans

--

Dave Peterson

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 quot;VBA based formulasquot; 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:= _
quot;C:\Documents and Settings\Administrator\My Documents\ISS RAW
DATA\Time\Call Summary.xlsquot;
Windows(quot;Call Summary.xlsquot;).Activate
Workbooks(quot;Call Summary.xlsquot;).Sheets(quot;Call
Summaryquot;).Range(quot;A1:T4000quot;).Copy _
Workbooks(quot;Conner Time Compliance.xlsquot;).Sheets(quot;Call
Summaryquot;).Range(quot;A1quot;)
Workbooks.Open Filename:= _
quot;C:\Documents and Settings\Administrator\My Documents\ISS RAW
DATA\Time\Rep Performance Analysis.xlsquot;
Windows(quot;Rep Performance Analysis.xlsquot;).Activate
Workbooks(quot;Rep Performance Analysis.xlsquot;).Sheets(quot;Rep
Performance Analysisquot;).Range(quot;a1:T4000quot;).Copy _
Workbooks(quot;Conner Time Compliance.xlsquot;).Sheets(quot;Rep Performance
Analysisquot;).Range(quot;A1quot;)
Workbooks(quot;Call Summary.xlsquot;).Close
Workbooks(quot;Rep Performance Analysis.xlsquot;).Close

What I would like is for the field managers to be able to save this
workbook in a specified folder along with quot;Call Summaryquot; and quot;Rep
Performance Analysisquot; then be able to run this...
My question now is, How would I write it so that the quot;Summary Sheetquot;
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,quot;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!HansIf 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 quot;Conner Time Compliance.xlsquot; 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 amp; quot;\quot; amp; quot;Call Summary.xlsquot;)

Set RepPerfWkbk = Workbooks.Open _
(filenameThisWorkbook.Path amp; quot;\quot; amp; quot;Rep Performance Analysis.xlsquot;)

'do you need conner time compliance to be opened?
Set ConnerTimeWkbk = Workbooks.Open _
(filenameThisWorkbook.Path amp; quot;\quot; amp; quot;Conner Time Compliance.xlsquot;)
'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(quot;call summaryquot;).Range(quot;a1:t4000quot;).Copy _
Destination:=ConnerTimeWkbk.Worksheets(quot;call summaryquot;).Range(quot;a1quot;)
'or
CallSummWkbk.Worksheets(quot;call summaryquot;).Range(quot;a1:t4000quot;).Copy _
Destination:=ThisWorkbook.Worksheets(quot;call summaryquot;).Range(quot;a1quot;)

'same question here...
RepPerfWkbk.Worksheets(quot;rep performance analaysisquot;).Range(quot;a1:T4000quot;).Copy _
Destination:=ConnerTimeWkbk _
.Worksheets(quot;rep performance analaysisquot;).Range(quot;a1quot;)
'or
RepPerfWkbk.Worksheets(quot;rep performance analaysisquot;).Range(quot;a1:T4000quot;).Copy _
Destination:=ThisWorkbook _
.Worksheets(quot;rep performance analaysisquot;).Range(quot;a1quot;)

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 = quot;quot;
on error resume next
teststr = dir(quot;c:\SummaryAnalysisCompliance\Call Summary.xlsquot;
on error goto 0

if teststr = quot;quot; then
msgbox quot;input file missing.quot; amp; _
quot;And give them a nice message about what they should doquot;
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.

quot; wrote:
gt;
gt; Dave,
gt;
gt; In the workbook I have 3 sheets (call summary, rep performance, PTO)
gt; that I reference back and place in the summary sheet, the 4th sheet (
gt; AND the Main Reporting sheet). That was the difference in regards to
gt; your ps.
gt; However; with that being said, you have given me enough information
gt; that I was able to change the subroutines and make them work in each
gt; case. At this point, I have not finished all the conversions from
gt; formulas in the cells to quot;VBA based formulasquot; and have reduced the file
gt; size from 6-7 megs to 966 KB which is one of the things I wanted to
gt; achieve, easier to email. The other was previously I had to copy the
gt; workbook into another and paste just the values, which was a pain to
gt; do! And then email....
gt;
gt; I believe that it is all a good thing at this point...
gt;
gt; The last thing I would like to do is be able to allow the field
gt; managers to utilize this workbook on their own...
gt; But, the opening lines in my code is:
gt;
gt; Workbooks.Open Filename:= _
gt; quot;C:\Documents and Settings\Administrator\My Documents\ISS RAW
gt; DATA\Time\Call Summary.xlsquot;
gt; Windows(quot;Call Summary.xlsquot;).Activate
gt; Workbooks(quot;Call Summary.xlsquot;).Sheets(quot;Call
gt; Summaryquot;).Range(quot;A1:T4000quot;).Copy _
gt; Workbooks(quot;Conner Time Compliance.xlsquot;).Sheets(quot;Call
gt; Summaryquot;).Range(quot;A1quot;)
gt; Workbooks.Open Filename:= _
gt; quot;C:\Documents and Settings\Administrator\My Documents\ISS RAW
gt; DATA\Time\Rep Performance Analysis.xlsquot;
gt; Windows(quot;Rep Performance Analysis.xlsquot;).Activate
gt; Workbooks(quot;Rep Performance Analysis.xlsquot;).Sheets(quot;Rep
gt; Performance Analysisquot;).Range(quot;a1:T4000quot;).Copy _
gt; Workbooks(quot;Conner Time Compliance.xlsquot;).Sheets(quot;Rep Performance
gt; Analysisquot;).Range(quot;A1quot;)
gt; Workbooks(quot;Call Summary.xlsquot;).Close
gt; Workbooks(quot;Rep Performance Analysis.xlsquot;).Close
gt;
gt; What I would like is for the field managers to be able to save this
gt; workbook in a specified folder along with quot;Call Summaryquot; and quot;Rep
gt; Performance Analysisquot; then be able to run this...
gt; My question now is, How would I write it so that the quot;Summary Sheetquot;
gt; knows which folder to in for the other sheets? i.e. what if a manager
gt; saves these files in c:\My Documents\My Reports.
gt; The code would not read,quot;Workbooks.Open Filename:=
gt; c:\MyDocuments\MyReports.... So how would I make this happen?
gt;
gt; And Thanks for the explanation earlier...that helped ALOT in
gt; understanding how things happen!
gt;
gt; Hans

--

Dave Peterson