Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 20th 07, 06:19 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 2,494
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Project - Second opinion please! Danny Excel Worksheet Functions 11 September 17th 07 11:03 PM
Need Opinion on Toolbar Creation surfbored Excel Programming 2 October 9th 06 10:19 PM
Opinion on Size/Length of Macro/Function/Sub Rob Excel Programming 2 August 27th 06 05:53 AM
Create your own poll for free and share your opinion [email protected] Excel Programming 0 June 3rd 06 09:54 AM
Your opinion CBrausa Excel Discussion (Misc queries) 1 May 18th 06 07:55 PM


All times are GMT +1. The time now is 12:06 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017