# Posts by filo65

could be check the AnalysisToolpack

filippo

simon,

I attach a spreadsheet. Could be I mistype something from german to english

filippo

## Files

Re: Lowering Memory Usage

How big is the file?

probably "vlookup" is killing your performance.

filippo

Just realized that in the second "RANDBETWEEN" the range MUST be 48-57 ( 0 to 9 ) and not 49-57 ( 1 - 9 )

filippo

try as well:

in A1 =CHAR(CHOOSE(RANDBETWEEN(1,3),RANDBETWEEN(49,57),RANDBETWEEN(65,90),RANDBETWEEN(97,122)))

copy right to J10.

in A2: = CONCATENATE(A1,B1,...,J1)

filippo

Re: Extract Dynamic Data From Closed Workbook

try

Code
``````Sub look_for_range()
Dim begArea As Range, endArea As Range
Range("A1").Select
Set begArea = Columns(1).Find(What:="URLs", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(3, 1)
Set endArea = begArea.End(xlDown).Offset(0, 1)

End Sub``````

filippo

Re: Extracting Dynamic Data From Closed Workbook

is/can the starting cell be identified with a particular sign ( header, etc. ) ?

could you post a couple of examples ( in the same workbook, different worksheets ) to see how this appears?

filippo

Re: Code For Triangle

can I ask you the logic of this? is it right that you loose more and more datas in your triangle?

filippo

Re: Multiple Rows Into One Summary Row

cannot a pivot table solve your problem?

Re: Fill without copying formatting

use Edit->PasteSpecial->Formulas

Re: Vba Range.formula

change

Code
``ws.Range("B15").Formula = "=IF(D12<>"Age","",IF(AND(YEAR(MID(D9,14,10))=YEAR(TODAY()),MONTH(TODAY())>=7)=TRUE,"Do Nothing",IF(MONTH(TODAY())>=7,DATE(YEAR(TODAY())+1,1,1),IF(MONTH(TODAY())<=6,DATE(YEAR(TODAY()),7,1))))) "``

Code
``ws.Range("B15").Formula = "=IF(D12<>"[B][COLOR="Red"]"[/COLOR][/B]Age[B][COLOR="Red"]"[/COLOR][/B]","",IF(AND(YEAR(MID(D9,14,10))=YEAR(TODAY()),MONTH(TODAY())>=7)=TRUE,"Do Nothing",IF(MONTH(TODAY())>=7,DATE(YEAR(TODAY())+1,1,1),IF(MONTH(TODAY())<=6,DATE(YEAR(TODAY()),7,1))))) "``

filippo

PS
I did just for one

Re: Deleted Lines But Formula Doesn't Change

having few infos it's difficult to say; could you post a scratch of both workbooks to see structure and possilble references?

filippo

Re: Deleted Lines But Formula Doesn't Change

it looks to me that you are referencing a cell in a different workbook.

If both workbooks are open you shouldn't be any problem, but I you have open only the one with the cells to be deleted the other one will not be "updated" with the new reference

filippo

Re: User Form Output

Zoosh,

Re: Working With Percentages over Many Cells

you could replace as well all the "9.57" with let say "\$B\$5" and enter there your value

filippo

Re: User Form Output

try something like:

Code
``````Private Sub seatCbo_Change()
ActiveSheet.Cells(Right(seatCbo.Value, Len(seatCbo.Value) - 4)+4, 1) = seatCbo.Value
End Sub``````

filippo

BTW

Code
``````With Seatcbo
'...
End With``````

could be written as

Code
``````With seatCbo
For i = 1 To 36
Next i
end with``````

Re: Vb - Cut Range Based On Find

have you tried with:

Code
``Rows(StartRow & ":" & EndRow).Select``

?

filippo

Re: Delete Whole Column If Header Contains &quot;x&quot;

thank you!

Re: Delete Whole Column If Header Contains &quot;x&quot;

pepperell

try:

Code
``````Sub remove_columns()
For i = ActiveSheet.Columns.Count To 1 Step -1
If InStr(1, Cells(1, i), "%") Then Columns(i).EntireColumn.Delete
Next i
End Sub``````

filippo

Re: Reset Dependent Drop Down List

in which line is the error generate?

filippo