Posts by VBA Noob

• Count Records

Re: Count Records

Try

=SUM(IF(FREQUENCY(IF(LEN(I2:I17)>0,MATCH(I2:I17,I2:I17,0),""),IF(LEN(I2:I17)>0,MATCH(I2:I17,I2:I17,0),""))>0,1))

VBA Noob

• Formula To Find Similar Data In Columns

Re: Formula To Find Similar Data In Columns

pangolin,

You could use this non VBA array

=SUMPRODUCT(--(LEFT(B9:B30)={"D","E","Y"}))

VBA Noob

• Text Formula? Postcodes

Re: Text Formula? Postcodes

Hi,

This might help

=IF(ISERROR(VALUE(MID(A3,2,1))={1,2,3,4,5,6,7,8,9,0}),LEFT(A3,2),LEFT(A3,1))

VBA Noob

• Add Row In Sum Formula

Re: Add Row In Sum Formula

Hi,

Quote

=SUM(OFFSET(\$A\$1,0,0,COUNTA(A:A),1))

VBA Noob

• Looping on Criteria

Re: Looping on Criteria

Hi Dave,

Adapted from code from Ron de Bruin

VBA Noob

• Looping on Criteria

Re: Looping on Criteria

Thanks Aaron,

Think I've cracked it now.

I used a unique filtered list and pasted the values into a template sheet which had the formulas and heading in Row 1 & 2 . Then I dragged the formulas down.

VBA Noob

• Looping on Criteria

Re: Looping on Criteria

Sounds like it would be useful.

Can you post the example ??

VBA Noob

• Looping on Criteria

Re: Looping on Criteria

Indeed I do but not sure where to start

Is there a good example of this anywhere or could you post some code I can try to adapt to

VBA Noob

Quote from Aaron Blood

Oh... what you need to do is create an array of unique names, then loop thru the array and find all instances of each name and copy/paste to a new sheet.

• Looping on Criteria

Re: Looping on Criteria

Sorry,

I've sorted the list on Column A. There are about 29 names in Column A from A14 down.

I want the Loop to find the first name A14 until it sees a change say A17 then copy range A14 to AW17 and paste special the values and formulas for A14 to AW17 to a new sheet. It should change the Sheet name to the person name in A14.

It should loop through until all 29 names have being done

VBA Noob

• Looping on Criteria

Hi,

Not to hot on looping so looking for help.

Starting cell is A14.

Want to paste special formulas and values A14 to AW14 to a new sheet and then loop through all cells down form A14 to see if they match A14 value. When it finds a new value it should repeat the loop.

Any help appreciated

VBA Noob

• Looping code & AutoFilter criteria

Re: Looping code &amp; AutoFilter criteria

Dave,

It works but paste values not my formula's.

Any tips ??

VBA Noob

• Looping code & AutoFilter criteria

Re: Looping code &amp; AutoFilter criteria

Thanks Dave.

Will give it a go today

VBA Noob

• Looping code & AutoFilter criteria

Re: Looping code &amp; AutoFilter criteria

Here's a simple example of what I'm trying to do

Contol sheet shows rough layout of data with mix of values and formulas

The "John" tab is the new sheet with Formatting and Values and Columns. Need autofilter each unique name in list and create a new sheet for each

I was going to then use Ron's "Mail every WorkSheet with address in A1 with SendMail" to send sheets once e-mailled I was going to delete all sheets to the left off Control sheet.

VBA Noob

• Looping code & AutoFilter criteria

Re: Looping code &amp; AutoFilter criteria

Here it is

Main sheet called Control Panel.
A14 down will have the range for AdvanceFilter.
120 rows with around 27 unique names.
Extra aheaders in A10:AW12 to copy.
Current region is A13:W133. Paste to A13 in new sheet (formatting, Column Widths values and formulas)
Hidden columns in T to AH. So will have to unhide and rehide in new sheet and orginial.
Will be e-mailing them so need to Kill them after I e-mail them so moving to begin ActiveSheet.Move Before:=Sheets(1)

Some code I'm being working on

Thanks for bearing with me on this one Markc

VBA Noob

• Looping code & AutoFilter criteria

Re: Looping code &amp; AutoFilter criteria

Sorry markc,

Was after the formulas to paste not the values

VBA Noob

• Looping code & AutoFilter criteria

Re: Looping code &amp; AutoFilter criteria

Hi markc,

Is it possible to paste special the values as I've got formula's in my selection

VBA Noob

• Looping code & AutoFilter criteria

Re: Looping code &amp; AutoFilter criteria

Thank you both.

VBA Noob