I am looking in column A of the sheets for a name and each name will have a value next to it in B. The name may appear multiple times per sheet,
so find next or all is necessary for a total across all sheets.

Assume In code below all is Dimmed correctly, how to add all the varOut's as they are found in column A .Offset(, 1) of each sheet in array.

Can the same code lines be used for constants and values from formulas or is a PasteSpecial.Values needed?

Once I have the correct code lines to add the varOut's I assume I can also use that code with FIND as the search method also?

Not stuck on this code if it can be quicker or more efficient.

Thanks.
Howard


MyArr = Array("Sheet2", "Sheet3", "Sheet4")

For i = LBound(MyArr) To UBound(MyArr)

With Sheets(MyArr(i))
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rngA = .Range("A1:A" & lr)

For Each c In rngA
If c = myName Then
varOut = c.Offset(, 1)

'/ add all the varOut's
'/ together as they are found
'/ across all the sheets
'/ and display total in msgbox

End If
Next 'c

End With
Next 'i

MsgBox "The total for " & myName & " is: " & varOut

Hi Howard,

Am Fri, 12 Sep 2014 22:39:26 -0700 (PDT) schrieb L. Howard:

I am looking in column A of the sheets for a name and each name will have a value next to it in B. The name may appear multiple times per sheet,
so find next or all is necessary for a total across all sheets.

try:
Sub Test()
Dim myArr As Variant
Dim i As Long, lr As Long
Dim rngA As Range, c As Range
Dim myName As String, Firstaddress As String
Dim valOut As Double

myArr = Array("Sheet2", "Sheet3", "Sheet4")
myName = "Name1"

For i = LBound(myArr) To UBound(myArr)
With Sheets(myArr(i))
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rngA = .Range("A1:A" & lr)
Set c = rngA.Find(myName, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Firstaddress = c.Address
Do
valOut = valOut + c.Offset(, 1)
Set c = rngA.FindNext(c)
Loop While Not c Is Nothing And c.Address < Firstaddress
End If
End With
Next 'i

MsgBox "The total for " & myName & " is: " & valOut
End Sub

Your sheets have indices. So you can call them by index and sumif 3D
with formula:

=SUM((T(INDIRECT("Sheet"&COLUMN(B:D)&"!A"&ROW(1:10 00)))="Name1")*(N(INDIRECT("Sheet"&COLUMN(B:D)&"!B "&ROW(1:1000)))))

and enter the array formula with CTRL+Shift+Enter


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Fri, 12 Sep 2014 22:39:26 -0700 (PDT) schrieb L. Howard:

Assume In code below all is Dimmed correctly, how to add all the varOut's as they are found in column A .Offset(, 1) of each sheet in array.

the following code writes the matches in Sheet1 column A & B
In column A the sheet name and the cell address, in column B the value

Sub Test2()
Dim myArr As Variant
Dim i As Long, lr As Long, n As Long
Dim rngA As Range, c As Range
Dim myName As String, Firstaddress As String
Dim arrOut() As Variant

myArr = Array("Sheet2", "Sheet3", "Sheet4")
myName = "Name1"

For i = LBound(myArr) To UBound(myArr)
With Sheets(myArr(i))
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rngA = .Range("A1:A" & lr)
Set c = rngA.Find(myName, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Firstaddress = c.Address
Do
ReDim Preserve arrOut(1, n)
arrOut(0, n) = .Name & "!" & c.Offset(, 1).Address(0, 0)
arrOut(1, n) = c.Offset(, 1)
n = n + 1
Set c = rngA.FindNext(c)
Loop While Not c Is Nothing And c.Address < Firstaddress
End If
End With
Next 'i

Sheets("Sheet1").Range("A1").Resize(n, 2) = _
Application.Transpose(arrOut)

'MsgBox "The total for " & myName & " is: " & valOut
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Fri, 12 Sep 2014 22:39:26 -0700 (PDT) schrieb L. Howard:

Assume In code below all is Dimmed correctly, how to add all the varOut's as they are found in column A .Offset(, 1) of each sheet in array.

or do you want to loop through all names and all sheets and then output
the names with the values as array? Then try following code. It writes
the names and the values in Sheet1 columns A&B:

Sub Test3()
Dim myArr As Variant, arrNm As Variant
Dim i As Long, j As Long, lr As Long, n As Long
Dim rngA As Range, c As Range
Dim Firstaddress As String
Dim arrOut(4, 1) As Variant
Dim valOut As Double

myArr = Array("Sheet2", "Sheet3", "Sheet4")
arrNm = Array("Name1", "Name2", "Name3", "Name4", "Name5")

For j = LBound(arrNm) To UBound(arrNm)
For i = LBound(myArr) To UBound(myArr)
With Sheets(myArr(i))
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rngA = .Range("A1:A" & lr)
Set c = rngA.Find(arrNm(j), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Firstaddress = c.Address
Do
valOut = valOut + c.Offset(, 1)
Set c = rngA.FindNext(c)
Loop While Not c Is Nothing And c.Address < Firstaddress
End If
End With
Next i
arrOut(n, 0) = arrNm(j)
arrOut(n, 1) = valOut
n = n + 1
Next j

Sheets("Sheet1").Range("A1").Resize(UBound(arrNm) + 1, 2) = arrOut

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Lots of options, thanks.

I'm pretty sure there is what I want in what you posted.

Will be away from my computer for a while and will look these over as soon as I can.

Thanks a bunch, Claus.

Regards,
Howard

On Saturday, September 13, 2014 1:32:34 AM UTC-7, Claus Busch wrote:
Hi again,



Am Fri, 12 Sep 2014 22:39:26 -0700 (PDT) schrieb L. Howard:



Assume In code below all is Dimmed correctly, how to add all the varOut's as they are found in column A .Offset(, 1) of each sheet in array.



or do you want to loop through all names and all sheets and then output

the names with the values as array? Then try following code. It writes

the names and the values in Sheet1 columns A&B:



Sub Test3()

Dim myArr As Variant, arrNm As Variant

Dim i As Long, j As Long, lr As Long, n As Long

Dim rngA As Range, c As Range

Dim Firstaddress As String

Dim arrOut(4, 1) As Variant

Dim valOut As Double



myArr = Array("Sheet2", "Sheet3", "Sheet4")

arrNm = Array("Name1", "Name2", "Name3", "Name4", "Name5")



For j = LBound(arrNm) To UBound(arrNm)

For i = LBound(myArr) To UBound(myArr)

With Sheets(myArr(i))

lr = .Cells(.Rows.Count, 1).End(xlUp).Row

Set rngA = .Range("A1:A" & lr)

Set c = rngA.Find(arrNm(j), LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then

Firstaddress = c.Address

Do

valOut = valOut + c.Offset(, 1)

Set c = rngA.FindNext(c)

Loop While Not c Is Nothing And c.Address < Firstaddress

End If

End With

Next i

arrOut(n, 0) = arrNm(j)

arrOut(n, 1) = valOut

n = n + 1

Next j



Sheets("Sheet1").Range("A1").Resize(UBound(arrNm) + 1, 2) = arrOut



End Sub





Regards

Claus B.


The code Sub Test3() works well for what I am looking for.

This works for the arrNM hardcoded.
arrNm = Array("Name1", "Name2", "Name3")


Trying to read F1:Fn into arrNm where F1 to F3 are Name1, Name2, Name3. (Or more)
This errors out
arrNm = Sheets("Sheet1").Range("F1:F" & LRow)

Howard

The code Sub Test3() works well for what I am looking for.

This works for the arrNM hardcoded.
arrNm = Array("Name1", "Name2", "Name3")


Trying to read F1:Fn into arrNm where F1 to F3 are Name1, Name2,
Name3. (Or more)
This errors out
arrNm = Sheets("Sheet1").Range("F1:F" & LRow)

What's the error?
How is arrNm declared?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sunday, September 14, 2014 9:03:35 PM UTC-7, GS wrote:
The code Sub Test3() works well for what I am looking for.



This works for the arrNM hardcoded.

arrNm = Array("Name1", "Name2", "Name3")





Trying to read F1:Fn into arrNm where F1 to F3 are Name1, Name2,

Name3. (Or more)

This errors out

arrNm = Sheets("Sheet1").Range("F1:F" & LRow)



What's the error?

How is arrNm declared?



Hi Garry,

Error message is:

Application-defined or object-defined

with entire line yellowed out


This is how I have it in a standard module

Sub Test3()
Dim myArr As Variant

'/ Need to read F1:F? into arrNm
Dim arrNm() As Variant

Dim i As Long, j As Long, LR As Long, n As Long
Dim rngA As Range, c As Range
Dim Firstaddress As String
Dim arrOut(4, 1) As Variant
Dim valOut As Double
Dim LRow As Long
myArr = Array("Sheet2", "Sheet3", "Sheet4")

arrNm = Sheets("Sheet1").Range("F1:F" & LRow)
'arrNm = Array("Name1", "Name2", "Name3")

For j = LBound(arrNm) To UBound(arrNm)

For i = LBound(myArr) To UBound(myArr)
With Sheets(myArr(i))

LR = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rngA = .Range("A1:A" & LR)

Set c = rngA.Find(arrNm(j), LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then
Firstaddress = c.Address
Do

valOut = valOut + c.Offset(, 1)

Set c = rngA.FindNext(c)
Loop While Not c Is Nothing And c.Address < Firstaddress
End If
End With
Next i

arrOut(n, 0) = arrNm(j)
arrOut(n, 1) = valOut

n = n + 1

Next j

'/Put Lookup string in A1 and sum of offset values in B1
Sheets("Sheet1").Range("A1").Resize(UBound(arrNm) + 1, 2) = arrOut

'/ Sum of lookup values in A1
'Sheets("Sheet1").Range("A1") = valOut
End Sub


Howard

And with Name1, Name2, Name3 in F1:F3 of sheet1.

Ok.., according to your code this..
arrNm = Sheets("Sheet1").Range("F1:F" & LRow)

is the same as this...
arrNm = Sheets("Sheet1").Range("F1:F0")

Also, this won't work...
arrOut(n, 0) = arrNm(j)

...because arrNm is a 1-based 2D array.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion