If then Help is greatly appreciated!

H

hansjhamm

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!"
 
D

Dave Peterson

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
 
H

hansjhamm

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
 
D

Dave Peterson

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

hansjhamm

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
 
D

Dave Peterson

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

hansjhamm

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
 
D

Dave Peterson

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

hansjhamm

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
 
D

Dave Peterson

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

hansjhamm

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
 
D

Dave Peterson

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