# Posts by ExcelChick

• ## Pivot table Calculated Field - Is this possible?!

Re: Pivot table Calculated Field - Is this possible?!

Are sumifs possible in calculated fields? Could I use something like this? So far it's not working

[TABLE="width: 60"]

[tr]

[TD="width: 60"]=IFERROR(SUMIFS(B3:B7,A3:A7,"Loads & Other Labor Costs")/SUMIFS(B3:B7,A3:A7,"Contract Hrs"),0)[/TD]

[/tr]

[/TABLE]

• ## Pivot table Calculated Field - Is this possible?!

Hello,

I am trying to create a pivot table with a calculated field that would effectively divide Loads & Other Labor Costs by Contract hours to get Loads & Other Labor Costs per hour.

See picture.
[ATTACH=CONFIG]57582[/ATTACH]

I don't know if this is possible since they both sit within the Metrics field?

any help would be awesome.

Thanks!

• ## Optimize VBA with multiple loops

Hello - I am trying to optimize this code so that I can easily call out this section of code multiple times. I cannot make it a sub procedure because it contains

Code
``If ActiveCell.End(xlDown).Row = 1048576 Then Exit For``

Code
``````'Dim this as something? or Set it? Make it a function?
counter1 = 1
Do Until counter1 > 9
If ActiveCell.End(xlDown).Row = 1048576 Then Exit For
ActiveCell.End(xlDown).Offset(z).ClearContents
ActiveCell.End(xlDown).Offset(z).Select
counter1 = counter1 + 1
Loop``````

Here's the current full code:

Thanks!

• ## vba clear contents of specific range in intervals loop

Re: vba clear contents of specific range in intervals loop

Here's what I ended up with using that seems to be working (in case others may benefit)

• ## vba clear contents of specific range in intervals loop

Re: vba clear contents of specific range in intervals loop

Then I would have to call out every range in column G - I am trying to avoid that by using an offset of -63 somehow, then looping up to repeat for the next section of column G all the way down.

• ## vba clear contents of specific range in intervals loop

Hello,

I am trying to delete specific cells in a pattern/interval. I do not want to use the ranges listed because I want it to loop all the way down the column.

'Clearcontents of G2:17 (Clear 16 cells)
'Clearcontents of G81:G88 (63 cells down from last clear, Clear 8 cells)
'Clearcontents of G152:158 (63 cells down from last clear, Clear 7 cells)
'Clearcontents of G222:228 (63 cells down from last clear, Clear 7 cells)
'Clearcontents of G292:298 (63 cells down from last clear, Clear 7 cells)
'Clearcontents of G364:370 (63 cells down from last clear, Clear 7 cells)
'Clearcontents of G434:455 (63 cells down from last clear, clear 22 cells)
'Clearcontents of G519:525 (63 cells down from last clear, Clear 7 cells)
'Clearcontents of G599:605 (73 cells down from last clear, Clear 7 cells)
'Loop to top of this for entire column

I don't have any code yet... any help would be appreciated!

Thanks!

• ## Filtering for multiple values VBA

Re: Filtering for multiple values VBA

I recorded the macro and selected those values - I realized that I have a 2nd filter on a different column that is causing it to leave only 3 of the 5 selections. Issue resolved.

• ## Filtering for multiple values VBA

Hello,

I recorded this macro to have 5 filter criteria, but when I run it it only selects 3 of the 5 criteria. Can someone please assist?

Code
``````Worksheets("Sup_Data").Range("\$A\$1:\$Q\$54436").AutoFilter Field:=9, Criteria1:=Array( _
"Contract Hrs", "Loads & Other Labor Costs", "Supervised FTE", "Total Labor Costs", _

Thanks!

• ## Paste values under certain number of rows with worksheet array loop

Re: Paste values under certain number of rows with worksheet array loop

Here's what I ended up using that worked!

• ## Paste values under certain number of rows with worksheet array loop

Re: Paste values under certain number of rows with worksheet array loop

Quote from patel;692117

LR is a row number, the can be integer or long, did you solve ?

I was not able to solve
I tried to Dim as both an integer and Long with the code I just posted above.
The result is that when it loops through the 2nd worksheet it just pastes over the previous pasted values.
And when I hover over LR in the code it says "LR = 1" So it just keeps pasting in row 2.

• ## Paste values under certain number of rows with worksheet array loop

Re: Paste values under certain number of rows with worksheet array loop

What do I dim LR as? an integer? It doesn't work if I dim it as a Range...

So are you proposing that my updated code should be:

• ## Paste values under certain number of rows with worksheet array loop

Hello,

I am trying to have the macro loop through the worksheets, copy data, paste to another tab, under the last set of data each time. The issue is that when I use the below, the column of data that was originally pasted in does not go all the way to the bottom of the rows where there is data in other columns.

Code
``.Range("D" & Rows.Count).End(xlUp).Offset(1)``

For example in the picture you can see there are blanks in column D but the data for the Quarters to the right are populated. (These Quarter columns are not continuous data either/have blanks so using the above code in a similar fashion won't work I don't think?)

[ATTACH=CONFIG]57541[/ATTACH]

The only way I can think of is to run through the first work sheet as listed below, then the other worksheets with something like:

Code
``Worksheets("Sup_Data").Range("D" & intgroup).Offset(1).PasteSpecial (xlPasteValues)``

But this only results in pasting over the same cells each time. I'd need each subsequent sheet to be intgroup *2 or something?

Any help would be greatly appreciated!

Thank you!

• ## Pivot Table with multiple columns

Re: Pivot Table with multiple columns

This is what I came up with after looking around the internet - I don't know how to make the pivot fields an array - but this is getting me what I need. Maybe this will help others!

• ## Pivot Table with multiple columns

Hello all,

I am trying to create a pivot table that has "Metric" in the Rows and the sum of Q1-Q4 FY13 & 14 into the columns. All of the numerical values sit under the headers Q1 etc. So the data looks like this:

[TABLE="width: 652"]

[tr]

[td]

Metric

[/td]

[td]

Q1 FY13

[/td]

[td]

Q2 FY13

[/td]

[td]

Q3 FY13

[/td]

[td]

Q4 FY13

[/td]

[td]

Q1 FY14

[/td]

[td]

Q2 FY14

[/td]

[td]

Q3 FY14

[/td]

[td]

Q4 FY14

[/td]

[/tr]

[tr]

[td]

[/td]

[TD="align: right"]101.819[/TD]
[TD="align: right"]95.60698[/TD]
[TD="align: right"]113.3032[/TD]
[TD="align: right"]157.2209[/TD]
[TD="align: right"]231.1385[/TD]
[TD="align: right"]401.4415[/TD]
[TD="align: right"]525.2051[/TD]
[TD="align: right"]473.9572[/TD]

[/tr]

[tr]

[td]

[/td]

[TD="align: right"]210.7658[/TD]
[TD="align: right"]56.87322[/TD]
[TD="align: right"]55.68585[/TD]
[TD="align: right"]15.40472[/TD]
[TD="align: right"]24.31954[/TD]
[TD="align: right"]39.38726[/TD]
[TD="align: right"]53.65847[/TD]
[TD="align: right"]48.42264[/TD]

[/tr]

[tr]

[td]

[/td]

[TD="align: right"]-1.73045[/TD]
[TD="align: right"]65.64151[/TD]
[TD="align: right"]135.621[/TD]
[TD="align: right"]59.11858[/TD]
[TD="align: right"]93.33092[/TD]
[TD="align: right"]151.1562[/TD]
[TD="align: right"]205.9247[/TD]
[TD="align: right"]185.8312[/TD]

[/tr]

[tr]

[td]

[/td]

[TD="align: right"]93.13438[/TD]
[TD="align: right"]15.52834[/TD]
[TD="align: right"]9.75809[/TD]
[TD="align: right"]53.07859[/TD]
[TD="align: right"]83.79554[/TD]
[TD="align: right"]135.713[/TD]
[TD="align: right"]184.8859[/TD]
[TD="align: right"]166.8453[/TD]

[/tr]

[/TABLE]

This is what I have - but it's not working

I would also like to format all the fields .NumberFormat = " #,##0" at the same time.
And rename the sheet "Supervised Pivot"

Can anyone assist?

Thanks,

• ## Fillup blank cells with cell below until column is populated

Re: Fillup blank cells with cell below until column is populated

Quote from S O;691715

True, as there is no loop, however Jindon's code assumes there is a value adjacent to the blank cell, which is why I'm assuming it didn't work originally.

This is correct - it was only filling up one cell - it wouldn't loop through to fill the remaining blanks.

Thank you all for your help!

• ## Fillup blank cells with cell below until column is populated

Re: Fillup blank cells with cell below until column is populated

This worked perfectly for me! Thank you!

• ## Loop code through certain worksheets

Re: Loop code through certain worksheets

I think I love you.

Thank you!

• ## Fillup blank cells with cell below until column is populated

Re: Fillup blank cells with cell below until column is populated

This fills down in the same fashion I'd like it to fill up. But when I change the -1 to a 1 it only fills up 1 cell.

• ## Loop code through certain worksheets

Hello,

I am trying to loop the following code for a total of 15 worksheets without copying and pasting that same code 14 more times for each worksheet. Right now it is only executing the code on the "CAN" tab. Is there a way to make it loop where indicated below?

The 15 worksheets are:
CAN
USA
ASG
Gallia
IGEM
LAT
NOR
SPAI
UKI
ANZ
ASE
China
IND
JPN
Skorea

Is it better to use Sheet(1) rather than the names of the tabs?

Thanks as always for your help!

• ## Fillup blank cells with cell below until column is populated

Hello,

I am creating a worksheet that will become a pivot table so I need the column G to be filled in for all rows. I would like it to loop through column G until it hits the last row (which may vary).
The data looks something like this
G2:G7 Blank
G8 Text to fill up
Repeats blanks and data

Would like to have text from G8 Fillup to G2:G7 or up until it hits another cell that has data in it. This is what I have right now - but it does not fill all the way up. It only fills up to G7:

Any help would be greatly appreciated!

Thanks!