Remember Me?

 Gary Keramidas external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 2,494 opinion on code / formula reiult

thanks for reading, but don't spend a lot of time on this. just wondering
which formula you'd use.
had to recreate some formulas to take into consideration the user entering a
space or lower case character. as an exercise, i did it 2 different ways. the
2nd way was faster, but the first way is a lot easier to decipher when you look
at the formula. probably never need the code again.

a little background
arrRow contains 5 elements, starting row numbers for each table, 4,
37,70,103,136. columns b-m are used.
the first one uses a loop for each column and row element, then autofills. the
2nd one just writes the entire range for each row element. B3 in this instance
contains Apr. both code examples return the same result from the formula.

first formula and code to create it:
=IF(UPPER(TRIM(Apr!\$P3))="M",Apr!G3,0)

For r = LBound(arrRow) To UBound(arrRow)
For c = 2 To 13
With .Cells(arrRow(r), c)
.Formula = "=if(Upper(trim(" & Cells(3, c) _
& "!\$P3))=" & arr(z) & "," & Cells(3, c)
& _
"!G3,0)"
End With
Next
.Range(.Cells(arrRow(z), 2), .Cells(arrRow(z), _
13)).AutoFill Destination:=.Range(.Cells(arrRow(z),
2), _
.Cells(arrRow(z) + 30, 13))
z = z + 1
Next
================================================== ===================
2nd formula and code that creates it:
=IF(UPPER(TRIM(INDIRECT(B\$3&"!\$P"& ROW()-1)))="M",INDIRECT(B\$3&"!G"& ROW()-1),0)

For r = LBound(arrRow) To UBound(arrRow)
With .Range("B" & arrRow(r) & ":M" & _
arrRow(r)).Resize(31)
.Formula = "=IF(Upper(Trim(INDIRECT(B\$3&" & _
"""!\$P""" & "& row()" & -1 - z & ")))=" &
arr(c) _
& ",INDIRECT(B\$3&" & """!G""" & "& ROW()"
& -1 - _
z & "),0)"
z = z + 33
c = c + 1
End With
Next

--

Gary

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Danny Excel Worksheet Functions 11 September 17th 07 11:03 PM surfbored Excel Programming 2 October 9th 06 10:19 PM Rob Excel Programming 2 August 27th 06 05:53 AM [email protected] Excel Programming 0 June 3rd 06 09:54 AM CBrausa Excel Discussion (Misc queries) 1 May 18th 06 07:55 PM

All times are GMT +1. The time now is 10:33 AM. Copyright ©2004-2019 ExcelBanter.