Visual Basic :: Countif() With Multiple Criteria && Multiple Sheets

Countif() With Multiple Criteria And Multiple Sheets
Hi!
I have 4 sheets in an excel workbook
The data starting from the sheet1 column B row 3 (B3) (the column A is empty only in the sheet1 not to the others three) and i dont know where the columns ending.
What i know is that the columns never be more than 1024 (256 columns x 4 sheets)

The data that there are in the cells, are numbers from 0 to 5.

The row 2 (B2, C2,..., IV2 etc) is using to make a group, so there are text numbers (01,02,03 etc ) for the purpose. So i will have 10 columns with 01, 50 columns with '02', 45 columns with '03' on the row2 and goes on.
But

What i want is:
I want to count how many 0,1,2,3,4 and 5 are for each cell grouping by the row group each time i choose.

So i want to count the the 0,1,2,3,4 and 5 for the group '01' or
count the the 0,1,2,3,4 and 5 for the group '03'.

Example
In a single sheet if i want to find the 0 and if i know where the columns of the group '01' starts and ends it would be like this

COUNTIF(INDEX('sheet1'!3:3,1,B2):INDEX('sheet1'!3:3,1,I2),0))

I hope you understand
Thanks in advance
Stathis

Importing Multiple Sheets From A Excel Spreadsheet Into Multiple Tables
hello all,

I am trying to code, but i am just stuck after importing one sheet. so here is the gist of what i need help with.

In a workbook at the start of the year (january) i will have 4 sheets and these sheets will keep increasing to 12 when the month is december. so i want is, sheet 1 (named abc) should go into table 1 (named abc), sheet 2 (named def)should go into table 2 (named def), sheet 3 (named xyz) should go into table 3 (named xyz). then sheet 4 through the next sheets till sheet 12 (named balance1, balance2, abalance3...so on till balance12) should all go into one table 'table4' (named balance) and all these sheets should keep appending starting from 1 then 2 all the way to 12.

I am able to put one sheet into one table and below is the code....i need help with the other part of my requirement

Code: ( text )
Dim cn As ADODB.Connection
Dim oRs As New ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim rsAccess As New ADODB.Recordset

' Open Excel Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Test.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With

' Open Access Connection
Set cnAccess = New ADODB.Connection
With cnAccess
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Documents and SettingskrishnamDesktopIntercompany Consolidation.mdb;"
.Open
End With

' Load ADO Recordset with Excel Sheet1Data

oRs.Open "Select * from [abc$]", cn, adOpenStatic
MsgBox oRs.RecordCount

' Load ADO Recordset with Access Data
rsAccess.Open "select * from tbl_abc", cnAccess, adOpenStatic, adLockOptimistic
MsgBox rsAccess.RecordCount

'Synchronize Recordsets and Batch Update
Do While Not (oRs.EOF)
rsAccess.AddNew
For i = 0 To 11 -----11 columns in table 1
rsAccess.Fields(i).Value = oRs.Fields(i).Value
Next
rsAccess.Update
oRs.MoveNext

Loop

End Sub

please help me so that i can move forward...

Autofilter With Multiple Columns And Multiple Criteria
Hi

I have a sheet of data, which I need to clear up. What I want to do is use the autofilter function to filter out the information that I need, leaving what I don't need visible and then delete the visible rows.

First filter is on column D, where I need to select all rows that are in "Unit1" and then filter on column C to select all rows that are NOT "Team1" or "Team2".

Code I have is:

vb/
Dim CheckRange As Range, DeleteRange As Range

Set CheckRange = Worksheets("Sheet1").Columns(7)
CheckRange.AutoFilter Field:=4, Criteria1:="Unit1"
CheckRange.AutoFilter Field:=3, Criteria1:="<>Team1", Operator:=xlOr, Criteria2:="<>Team2"

Set DeleteRange = CheckRange.SpecialCells(xlCellTypeVisible).EntireRow
DeleteRange.Delete
/vb

However this does not work. It filters out all the non "Unit1" rows fine, but then applied a filter to Column C that does nothing!

Can someone help put me out of my misery?

Using CountIf With > 1 Criteria Field
Hi all,

Is it possible to use the countif function with more than one criteria field.

For example, I have a worksheet with > 1000 rows. I would like to calculate how many times times the word "COMPLETED" shows up in column K and when column D is equal to "JULY 29, 2004".

If, for performance reasons, there is a better way (e.g. AutoFilter) than using the CountIf method, I'm open to suggestions.

FYI, this is being initiated by a button push and I'm using Excel 2000.

thanks in advance!

Joey

Multiple Criteria
Hi I have the following table:

A B C D
1 Standard
2
3Fixed/
Set-upMin Charge ppm
4 Local

And data as follows which i want to read into the above
DescriptionTime ppm Fixed/Set-up Min Charge
LocalStandard1.00 10 100
Local Evening 2.00 20 200

so in b4 should be 10 c4 - 200 d4-2.00 understanding a4 contains Local and b1 is Standard (Evening is in E1 with Fixed etc in E-G)

Hope this makes sense.

Thanks

ADO Filter Using Multiple Criteria
I am writing a database application using the data environment. In one sub, I am attempting to locate all records with one recordset using two criteria based on fields in that recordset. Those fields are being compared to variables (type long) defined and filled earlier in the sub. It goes something like this:

DB.rsRecordset.Filter = "Trunk = " & TrunkNumber & " AND Circuit > " & MaxCircuit

Trunk and Circuit are both Long fields in rsRecordset.

The problem is that it's returning zero records when there are some that meet both criteria.

Am I doing something stupid?

Help With Datareport - Multiple Criteria
i have a prob with datareport... i have taken care of the sql queries on the data environment for my two reports...

my first report has one criteria = it will report all employee information for a certain paydate

the second report has two criteria = it will report all employee information from a certain department at a certain paydate

so, what i did was create a form where i have a combobox where i will store all existing paydates - my question for this is: do i need to create a recordset that will populate this combobox or directly from the query i created at the dataenvironment? if the latter works, how do i do this? second, how do i make out code that will show report according to the data clicked on the combobox?

coz this is the only thing that i was able to come up with for the combo click event (where the criteria - paydate,department is contained)

Code:
With DataEnvironment1
If .rsReportEmployee2.State = adStateOpen Then .rsReport_Employee2.Close
'of course there has to be a code here and i don't know how to start it
DataReport1.Show
End With
please help me out... tnx.

Multiple Criteria Searching
good day!

i'm only a beginner and i just want to ask what is the fastest and easiest way to search for a record that supports multiple criteria.. is it dao, ado, etc? i use msaccess..

what method to use and how will i be able to use it in outputting the results?

i really need your help.. thanks in advance..

Multiple Criteria Search
Hey there. I'm using ADO to search a db of clients. I'm able to search via only the Client ID, but I need to be able to search for the ID First of Last name, or all 3 at once depending on what information the user knows. I'm sure it's relatively basic, but I'm just a little new to the whole thing. Thanks!

If this is too vague let me know.

Sorting With Multiple Criteria
I need some help trying to sort my worksheet by more than 3 columns. Is that something that can be done easily with a macro in excel?

Thanks,
Alex

Multiple Sheets In CSV
Hi,

I am using the following code in VB6 to generate a CSV file that opens in MS EXCEL :

CODE :

Dim printLINE as String
Dim ctr as Integer
Open "c:File1.CSV" For Output As #1
For ctr = 0 To 20
printLINE = printLINE & ctr & ","
next ctr
Print #1, printLINE

This generates a csv file by the name File1.csv with only one sheet by the name File1.

My question is that how can i have more that one sheet e.g. File1 & File2 within the same csv file File1.CSV?

Does any body have any idea ?

Arun

Link Forms With Multiple Criteria
the problem is that i need to open the forms with all the records where klasID = number(1) and all the records where klasID = number(2) .. the 'and' part of the statement works fine, but the or won't work.
anyone got an idea?

Code:
stLinkCriteria = "[KlasID]=" & "nummer(1) Or nummer(2)" & "And [VakId] =" & Me.VakID '& "Or [KlasID]=" & nummer(2)
DoCmd.OpenForm "frmPuntenKlasVakLeerling", , , stLinkCriteria

Multiple Criteria With Find Method
I use the Find method in different (VB/Excel) projects.
Now i'm wondering if it is possible to use this method when using more than 1 criteria.

Lets say i want to search thru a column after all cells containing values between "10" and "30".
So the code has to search after all cells with values >= "10" And <= "30"
in a column so i can do some stuff with these cells.

Is this possible using the Find method or is this only possible using the
For Each X In Column.....
If X.Value >= loval And X.Value <= hival Then... way ??

I've never seen an Find method example using multiple criteria and i dont now it is possible thats why i post this question on the forum.

I've been playing around with the "normal" Find method code to see if i could change it in to what i want but i dont get it working.

Any help or good idea would be great.

italkid.

AutoFilter With Multiple Criteria Problem
I am trying to clean up a telephone call record file of 10 fields. With help from XtremeVB I managed to get a macro working that deletes rows containing a 1 in the second column (in-bound calls). Now I would like to delete any records in column 10 that have less than 11 characters. I have been unsuccessful in finding any examples of how I can accomplish this and lack the ability to interpret the VB Help relating to this. After blundering around with AutoFilter for a while I have exhausted any clues that I had.

Code:
Dim shtRecords As Worksheet
Dim rngData As Range

Set shtRecords = Application.ActiveWorkbook.ActiveSheet
Set rngData = shtRecords.Range("a11").CurrentRegion

With ActiveSheet

With rngData
.AutoFilter field:=2, Criteria1:="1"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With
End With
I would appreciate any help in getting me on track.

Thanks,

Ray

Multiple Criteria In Sumif Function
I am using an excel function worksheetfunction.sumif in a VB6 application. I want to sum values in a column of an excel sheet by using two sets of criteria.

for example,

column "C" should be summed only if the text in column "A" is "IP" and corresponding to this in column "B" the text is "Inforce".

Col A Col B Col C
IP Inforce 10
EP Inforce 20
IP Paidup 25
GB Inforce 35
IP Inforce 55

the code should return a sum of (10+55) = 65 ( first row and last row)

Any suggestions on how two sets of criteria and two sets of ranges can be given in a sumif function using VB6?

Searching Access DB Using Multiple Criteria
Hi all, the saga continues.

I'm creating a search form and I want the user to be able to select multiple criteria.

Now, I'm able to search successfully (well sort of see below) if i limit the query to one field. However if want to run it with 2 or more fields and I leave one of the textboxes blank if comes back with a missing operator error: var2 = ""

So how do I structure the query to ignore a field if it is empty and include it in the search if it has data in it?

Also if svar is a numerocal value then it runs fine, but if it is a text value then I get a : No value given for one or more required parameters error.

The code I am using is:

Dim rs As New ADODB.Recordset
Dim sql As String
Dim svar1 As String

svar1 = var1.text

sql = "SELECT * FROM table1 WHERE var1 = " & svar1 & ""

rs.Open sql, sConn, adOpenKeyset, adLockOptimistic, adCmdText

rs.MoveFirst

Set MSH.DataSource = rs

MSH.Refresh

rs.Close

Set rs = Nothing

Upddate Query With Multiple Criteria
Hi
I'm attempting to make an Access Update query that updates 2 different fields in a table, but using different criteria for each field. When I set it up how i think it should like in the query designer of Access it changes my criteria to an And statement, which I do not want. Here is how I believe the SQL code should look, but I'm getting a syntax error

Code:
UPDATE tblDailySalesTmp INNER JOIN tblSalesReport ON ([tblDailySalesTmp].[Chain]=[tblSalesReport].[Chain]) AND
([tblDailySalesTmp].[Sales Date]=[tblSalesReport].[Sale Date]) SET tblSalesReport.[Current Yr EVDY Net] =
[tblSalesReport].[Current Yr EVDY Net]+[tblDailySalesTmp].[Net Sales]WHERE (([tblDailySalesTmp].[Labels]="TOTAL EVERYDAY PRODUCT")
SET tblSalesReport.[Current Yr SEAS Net] = [tblSalesReport].[Current Yr SEAS Net]+[tblDailySalesTmp].[Net Sales]
WHERE ([tblDailySalesTmp].[Labels]="TOTAL SEASONAL PRODUCT"));
Any help would be great!

Recordset.find, Multiple Criteria
hi everybody,

I want to do the following:

rsVerantwoordelijke.Find "ver_p_id = " & rsPersoon("p_id") & " AND ver_v_id = " & rsVereniging("v_id") & ""

now this returns a 3001 exception, wrong type etc...

with a single criteria this works fine: rsVerantwoordelijke.Find "ver_p_id = " & rsPersoon("p_id")

anyone suggestions?

thx

Please Help Me Filtering Records With Multiple Criteria
please help me!!!!

Code:

total1 = "'" & Val(cboselect.Text) + 1 & "-" & Combo1.Text & "-" & Combo3.Text & "'"
total2 = "'" & Val(cboselect.Text) + 2 & "-" & Combo1.Text & "-" & Combo3.Text & "'"

that's my declaration for strings total1 and total2

Code:

rsmasterview.Filter = "[lastorder] = " & total1 Or " & total2 -- this is wrong!

this code is for the filtering the records

my problem is that what code should i use where the field LASTORDER will filter 2 criterias

thnkzzz very much!!!!

Using Find Method With Multiple Criteria
I' tryring to use FIND METHOD (ADO) keeps giving me

RunTime Error "3001"
Argument are of the wrong type, are out of acceptable range, one are in conflict with one another

I've tried various techniques still no luck. Is it a BUG or not possible to use this method with multiple criteria using AND
anybody know's the systax.?

dim xstring as string
xstring = "Colom1='13' AND Colom2='XX'" (failed)
xstring = "(colom1='13' AND colom2='XX')" (failed)
xstring = "colom1='13 " ' " AND "colom2= ' XX " ' " (failed)
xstring = "colom1 like '13' AND colom2='XX' " (failed)

the final Syntax:

rs.find xstring, 0, adSearchforward, 1

Range On Multiple Sheets
Hi, I have to make a range that is on two worksheets, is that possible? I have a sub that creates the range on one sheet. I need the same group of cells on a different worksheet added to that range. Here is the sub:

Code:
Function month_rng_finder() As Range

Dim wks As Worksheet, cell As Range, rng As Range, rc As Long, S As Date

Set wks = Worksheets("sheet 1")
rc = wks.Cells(Rows.Count, 1).End(xlUp).Row
Set cell = wks.Cells(rc, 1)
If cell.Row < 30 Then
Set month_rng_finder = Range(Cells(3, 1), Cells(rc, 1))
Else
Set month_rng_finder = Range(Cells(cell.Row - 30, 1), Cells(rc, 1))
End If

End Function

if the range on sheet 1 is for example b2:h30 then i need month_rng_finder to be sheet1 b2:h30 and sheet 2 b2:h30.

Thank you

Selecting Multiple Sheets
Hi, I have a listbox that displays all the sheets in an excel document. Is there a way to select multiple sheets and do different calculations with the cells? Example: I have data that I want to add up on 4 different sheets, how would I do that? Thanks for your help.

PDFing Multiple Sheets
Hi,

Can someone point me to where I can get VBA code to pdf multiple sheets in Excel into one pdf file??

If i do it manually i have to pdf each sheet individually and then put it together in acrobat which is very time consuming.

Thanks.

Copying Multiple Sheets
I have several opened excel files, each file has only one sheet. I need to copy each sheet of each file to a new book. I wanted to create a loop to do this.
Any idea?

Searching Multiple Sheets
I have come across another problem whilst making my spreadsheet. I have used this forum before where I was able to create the following code from what I learnt. This allows me to search a sheet for a value that I would be able to enter into an input box. I have now run into another problem. I now cannot work out the code that is needed so that it will search more than one sheet. I will need it to search 5 different sheets. I am guessing that it is something to do with a “loop” but I cannot find any information on this to make it work. If anyone could give me the pointers to make this code search multiple sheets I would be very grateful. I am sure that it is something very simple to do, but as I am new to using VB Code it is causing me a few problems. The code that I currently have to search one sheet is shown below. Thank you in advance.

Code:
Sub CopyQuote()
Application.ScreenUpdating = False
Dim rngOrder As Range
Dim lngReferenceNumber As Double
lngReferenceNumber = Application.InputBox( _
Prompt:="Enter reference number", _
Title:="Enter reference number", _
Type:=1)
Set rngOrder = Worksheets("Quote History").Range("A1:AI1000").Find( _
what:=lngReferenceNumber, _
lookat:=xlWhole)
If rngOrder Is Nothing Then
MsgBox "Quote not found"
Else
With rngOrder
.Parent.Activate
.Activate
If MsgBox("Do you want to copy this quote?", vbYesNo, "Copying Quote...") = vbYes Then
.EntireRow.Copy
Sheets("Data for Retrieve Quote").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Retrieve Quote").Select
Else
MsgBox "Quote not copied"
Sheets("Main Page ").Select
Application.ScreenUpdating = True
End If
End With
End If
End Sub

Help Please {Combine Multiple Sheets Into One}
Hi, this is my first time posting on this board and I am relatively new to programming with visual basic. I am an undergaduate who is using Visual Basic 6 to build a program that will compile 72 individual excell sheets into a single excel document. A graduate student who was part of my research group has already made a program that works for 61 files; however, every attempt I have made to alter the program has failed to produce a working program. Does anyone know how I can solve my problem?

Scaling For Multiple Sheets
My excel doc has about 200 sheets. I’m trying to change the Scaling for all sheets at once (Adjust to_ _ % of normal size).
I have selected all sheets. However, only the fist sheet is changed.

How can I fix this?
Thanks

Excel 2002

Multiple Sheets In An If Statement...
Hi everyone, I'm using this line of code:

Code:
If mySht.Name <> "NAGSHEET" Then
to exclude the sheet "NAGSHEET", is it possible to exclude 2 sheets? I've tried:

Code:
If mySht.Name <> "NAGSHEET" And "xSetup" Then

If mySht.Name <> "NAGSHEET" Or "xSetup" Then
and neither of those work....
Any ideas??

Thanks,

Dave M.

Accessing Multiple Sheets
iam working on vb front end and access bE project
the access db has 4 tables named week1,week2,week3,wek4. All the tables have similar fields .When i want to update table i want to use one set of cods to update the selected table
the table selection is done thro optionbuttons. Though i managed to make it separate coding i am interested in using a common function/sub and passing the parameters
The code i have written is as follows

Dim db As Database
'Public comrs As Recordsets
Public rsweek1 As Recordset
Public rsweek2 As Recordset
Public rsweek3 As Recordset
Public rsweek4 As Recordset
Dim ws As Workspace

Private Sub Form_Load()
Set db = OpenDatabase("C:Man hourMan hour.mdb")
Set rsweek1 = db.OpenRecordset("week1", dbOpenDynaset)
Set rsweek2 = db.OpenRecordset("week2", dbOpenDynaset)
Set rsweek3 = db.OpenRecordset("week3", dbOpenDynaset)
Set rsweek4 = db.OpenRecordset("week4", dbOpenDynaset)
End Sub

Private Sub CmdAdd_Click()
If OptWK1 = True Then
rsweek1.AddNew
ElseIf OptWK2 = True Then
rsweek2.AddNew
ElseIf OptWK3 = True Then
rsweek3.AddNew
Else
rsweek4.AddNew
End If

TxtDate.SetFocus
End Sub

Private Sub CmdUpdte_Click() ---- similar codes for week 3 and 4
If OptWK1 = True Then
write_data_wk1 ---------------[1]
ElseIf OptWK2 = True Then
write_data_wk2----------------------[2] takes to anothr sub same
as write_data_wk1()

End Sub

Public Sub write_data_wk1()
hrstotal = Val(TxtNorhr.Text) + Val(TxtOthr.Text)
If rsweek1.EditMode = 1 Or rsweek1.EditMode = 2 Then
rsweek1.Fields("WK NO") = "Week 1"
rsweek1(2) = TxtDate.Text
rsweek1(3) = TxtWon.Text
rsweek1.Fields("WO Type") = wotype
rsweek1.Fields("C C") = sCC
rsweek1.Fields("HRS-N") = CDec(Val(TxtNorhr.Text))
rsweek1.Fields("HRS-OT") = CDec(Val(TxtOthr.Text))
rsweek1.Fields("HRS-TOT") = hrstotal
rsweek1.Fields("Report text") = TxtReport.Text
rsweek1.Update
End If
End Sub

----- written same code for week 2,3&4 is there any other method to write one procedure / function so that [1] and [2] are not required

tks

Excel Multiple Sheets
Hi,
Is it possible to create a report in Crystal report from Excel that has multiple sheets. All sheets should be displayed in the report. Can this be accomplished?
Thanks

Creating Multiple Sheets
I need some help creating and accessing multiple Sheets within one excel file.

I have a variable number that indicates how many Sheets I need to Create.

This is my code:

Set XLApp = New Excel.Application 'Create a new instance of Excel
Set XLBook = XLApp4.Workbooks.Add 'Create a new Workbook
XLBook.SaveAs ("c:Book.xls")

For i=1 to number
Set XLSheet = XLBook4.Worksheets.Add
XLSheet.Name = "Sheet" & i
Set XLSheet = XLBook4.Worksheets(i)

....fill in sheet....
Next i

Say my number= 5, then i should have a file called "Book.xls" with 5 Sheets names "Sheet1, Sheet2, Sheet3, Sheet4, Sheet5) How can I do this?

Multiple Sheets Selection~ Help
Hi, sorry to be such a rush cos I found I am in death meat within 2 more days to go before deathline, beside have a last try on here. I have a run time error 1004 while doing this... in Excel (Version 2000 (9.0.4402 SR 1) runing VB.

1) If clicked on ¡®Shift¡¯ and highlight > 1 worksheets then clicked on any cell, the Runtime Error 1004 appeared

The error is from :
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'JL 19/08/02: Added for SIR-2226
If Target.Column <= 5 Then
ThisWorkbook.ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Else
ThisWorkbook.ActiveSheet.Unprotect
End If
End Sub

However, I need this to protect the first 5 column for read only.

2) Whenever trying to save the repayment schedule onto the local desktop, the following error message is prompted and the excel will be killed off. Not able to save repayment schedule. Tested that this error message only get prompted if file is saved as the default ¡®Ms Excel 97-2000 & 5.0/95 Workbook¡¯.
If changed file type to ¡®Ms Excel Workbook¡¯ then no error is prompted.

Your expertise and lending a helping hand a much much appreciate. Any guidance and advices are welcome too. Thanks again.

Cheers,
Allan
weiloon_acc@yahoo.com

Userforms On Multiple Sheets?
I have a userform which gathers inputs from the user and then tries to find them in a spreadsheet. Because Excel cannot display all the days in the year because of row limitaions being 256, i put each month on separate sheets within the workbook. Is it possible to have this code find the data in the sheet which is currently selected? If so, how would I go about it???

Many Thanks

T

Excel Multiple Sheets
Hi,
Is it possible to create a report in Crystal report from Excel that has multiple sheets. All sheets should be displayed in the report. Can this be accomplished?
Thanks

Search Multiple Criteria In Data Report
Hi, I've already set up my data report. I need to know the syntax how to search multiple criteria in a data report. All I know is to search only one criteria. Lets say I need to search a name which possess an ID number of this, a company of this, and an employee ID of this...

Data Control And Multiple Search Criteria
I haven't yet learned object programming so I want to know if there is a way to search for a record using multiple search criteria using the data control and an access database. I have a Weight From and Weight To column that is used as a weght range in one of my tables in an Access DB. A user will enter a weight into the program and the program has to search the Table and determine which weight range the Weight entered falls between. Thanks in advance for any help.

Multiple Excel Sheets To Access
I have 10 excel files with up to 500 worksheets in each file. I am familiar with TransferSpreadsheet...

I can loop thru files in a folder, but once I get to the excel spreadsheet, how do I loop thru each worksheet?

Pick Multiple Sheets In Excel
Does anyone know how I can pick multiple excel sheets that have date names, could be any number of sheets, and copy them to another workbook?
Also how do you pick multiple cells from multiple sheets and do a calculation on one sheet.
I know this sounds easy to some of you but I'm new.
Thanks

Search Multiple Excel Sheets
Hello. I have an Excel macro that searches all worksheets (expect Sheet3) in the open workbook for the text "new" Upon finding "new" the entire row is copied and then pasted into Sheet3. This is working fine.

However, I am getting an error message (Object variable or With block variable not set) when a worksheet does not contain the text "new"

Mode is as follows:

Code:
Dim wSht As Worksheet
Dim allwShts As Sheets
Dim creditAdj As String
Dim startRow As Integer
Dim myRange As Range

Set allwShts = Worksheets

For Each wSht In allwShts
creditAdj = "new"
If wSht.Name <> "Sheet3" Then
wSht.Activate
startRow = wSht.Columns("B").find(creditAdj, _
LookIn:=xlValues, LookAt:=xlWhole).Row
Range("B" & startRow).Select
Selection.EntireRow.Copy
Worksheets("Sheet3").Activate
Set myRange = ActiveCell
myRange.PasteSpecial
ActiveCell.Offset(1, 0).Select
End If
Next wSht
I tried moving the find function out of the loop but I can't seem to get the syntax correct. I am not even sure if I should move it out of the loop.

Any help would be much appreciated. Thanks

Unprotecting Multiple Sheets In Excel
Does anyone know the answer?

I am trying to unprotect multiple sheets within Excel. Selecting the sheets is easy enough, however I have been unable to remove protection from all the selected sheets.
Any help would be greatfully received.

Edited by westbrookrj on 09/19/01 07:47 AM.

Unhide Multiple Sheets At One Time
I'm trying to "Unhide" sheets using an array... I know this will work for "Hiding" but trying to "unhide" gets a "visible property" error

Code:
Sheets(Array("Weekly Report Data", "Total Project Data", "Formula Sheet")). _
Visible = True

Thanks...

Display Same Object On Multiple Sheets
Is it possible to display the same textbox object (or other embedded objects) on multiple sheets, without placing a copy on each sheet.
I just can't find a way to do it.....

Øystein

Reference Multiple Sheets In An Array, I Think!!
Hi,
I have a spreasheet with several worksheets. Each sheet has the same layout - a pricing grid - but i have a sheet for each different supplier. This means that =MIN(Sheet1:Sheet7!E6) will find me the lowest price out of all the suppliers for the product referred to in cell E6. However, i need to return the name of that supplier. I have used vlookup and hlookup before but obviously they are not going to work with multiple sheets. I figured that i could do it using an array formula but i haven't got much experience in using these and all my attempts have failed. What can i do?

Perform Operation On Multiple Sheets
I would like to delete several sheets but the exact number is unknown. My code creates several worksheets after receiving input from the user. I would like to delete these sheets without deleting a few hidden worksheets that always exist. For example, my code creates several sheets named differently, like "AMFR", "AMCS" which I would like to delete, but I would not like to delete a hidden sheet called "Configuration." At the momen I'm using code something like:
For each ws in ActiveWorkbook.Worksheets
If ws.name<>"Configuration" then
ws.Delete
End if
Next

I had the idea that it's faster to refer directly to all the sheets (without using a loop) and then delete them. (I'm not sure if this assumption is true, that it's faster.) For example I'd like to have code like

Sheets(Array(1,sheets.count-2).Delete

This would avoid deleting the last "Configuration" sheet because I know it's always the last one in the workbook. However, this will only delete for example Sheets 1 AND Sheets 3, not Sheets 1-3 as I would like. Is it possible to delete an array of sheets using a variable? I would like to avoid using a loop but maybe it's not possible. Thank you!

Copying Multiple Sheets With Links
when I copy 10 sheets from one data base to another I get a constand link error. the error states that I have links to the original database. How do I remove this link. The original database has zero links?

Tks

Loading From Multiple Sheets On Another Workbook
Hi
I'm trying to extend an existing function I wrote to use different sheets on a different workbook, instead of the active workbook.

The function below did work when using 'local' sheets. But I want to load all my data into a another xls file and load/read from it as and when required. I use multiple sheets a lot and don't want to have to add the same sheets to each of my workbooks each time i want to use the data.

It had now stopped working. It works for a split-second if I manually load the test.xls file, but after a sec it refreshs and returns error values.

Also, as you can see, I'm trying to get it to return "n/a" if it can't find a matching set of values, instead of #VALUE!.

Any ideas anyone?

Code:
Public Function lookupref(ref As String, dateref As Date, season As String, reqdcol As String)
Application.Volatile

'Load and activate correct season sheet
Dim wbTest As Workbook
tmpfile = "c: est.xls"
Set wbTest = Workbooks.Open(Filename:=tmpfile)
wbTest.Activate

'Get size of dataset
lastrow = ActiveWorkbook.Worksheets(season).Cells(2, "A").Value

'Loop data
lookupref = "n/a"
For a = 3 To (Int(lastrow) + 2)
tmpflt1 = UCase(ref)
tmpflt2 = UCase(ActiveWorkbook.Worksheets(season).Cells(a, "B").Value)
tmpdate1 = dateref
tmpdate2 = DateValue(ActiveWorkbook.Worksheets(season).Cells(a, "A").Value)
'MsgBox tmpflt1 & " " & tmpflt2 & " " & tmpdate1 & " " & tmpdate2
If (tmpflt1 = tmpflt2) Then
If (tmpdate1 = tmpdate2) Then
'MsgBox "Match"
lookupref = ActiveWorkbook.Worksheets(season).Cells(a, reqdcol).Value
Exit Function
Else
lookupref = "n/a"
End If
Else
lookupref = "n/a"
End If
Next a

ActiveWorkbook.Close
End Function

Using Multiple Excel Sheets Within One Workbook
I am doing a program that gathers information from different cells in Excel, and inputs them as different variables.

Right now, I've got a scarce understanding of how to use excel with VB.net, and although I've got my program working properly, I havent been able to figure out how to tell it to get Cell x,y from sheet a, and then get cell x,y from sheet b.

If anyone could help me out here, by either an explanation of how this should work, or even a sample code that makes this work I can probably take it from there.

I'd appreciate any help I can get. Thanks everyone!

Paste Values For Multiple Sheets
I am trying to write a command that will unprotect all worksheets, copy all cells on each worksheet, paste the values on the same sheet (replace any formulas with their values), re-protect all worksheets and then save the file. The code I am using is below. The protect, unprotect, and save all work fine. What I can't get to work is the copy and paste. It will only copy and paste the active sheet and will not switch to the next sheet. I am not an expert in VBA by any means, so this may be an easy fix.
Help!

Sub CommandButton1_Click()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="password"
Next ws

Application.ScreenUpdating = True

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Next ws

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="password"
Next ws

ActiveWorkbook.Save

End Sub

Copy Sheets From Multiple Excels To Another
Hi guys, I hope you can help.

I am attempting to use a VB macro in Excel to import sheets from other open Excel workbooks to this one. I have found the code to move or copy the sheets from one document to another fine. My issue comes when I need to specify the workbook the sheets are to be copied from. Although the workbook to be copied to will have a single constant name, the workbook to be copied from could be anything. To solve this I'm trying to get an user option box so they can selct which book to copy from.

I have managed to do this for the sheets within the book by using:

"Function Go2sheet()

Dim mySht As Single

myShts = ActiveWorkbook.Sheets.Count
For i = 1 To myShts
myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
Next i

mySht = InputBox("Select sheet to go to." & vbCr & vbCr & myList)
Sheets(mySht).Select

End Function"

When I have tried to adapt this to return the workbook name it trips up with an error. I think this is due to the j valueas adding 1 to the name does not equal the next name.

"Function Go2Workbook()

Dim myWorkbook As Single

myWorkbook = Windows.Count
MsgBox (myWorkbook)
For j = 1 To myWorkbook
myWBList = myWBList & j & " - " & ActiveWorkbook(j).Name & " " & vbCr
Next j

myWorkbook = InputBox("Select workbook to copy from (Enter number only)" & vbCr & vbCr & myWBList)

End Function"

Error is 'Error 438 - Object doesn't support this property method'

Any help with this would be greatly appreciated.

Paul