# Search:

Type: Posts; User: pgc01; Keyword(s):

Page 1 of 6 1

1. ## Thread: Area Chart Problem

by pgc01
Replies
4
Views
69

### Re: Area Chart Problem

Hi

The formula is not to be written in a cell. YOu should define a name with it.

In ribbon->formulas->Name Manager press New and enter

Name: Series1Y
Scope: Sheet1 (or other worksheet if...

by pgc01
Replies
5
Views
93

### Re: Issue scrolling on a spreadsheet

Sorry, I don't know what the problem may be.

You could have some vba that would restrict the scroll during a certain time when the workbook opens but you seem to know your way around vba and so...
3. ## Thread: Area Chart Problem

by pgc01
Replies
4
Views
69

### Re: Area Chart Problem

Hi

For ex. for the first series define the name

Name: Series1Y
Refers to: =OFFSET(Sheet1!\$B\$3,,,,COUNTIF(Sheet1!\$B\$3:\$F\$3,">0"))

and then in the chart use for the series values:
...
4. ## Thread: Excel VBA: Copy multiple Column of a Table

by pgc01
Replies
2
Views
69

### Re: Excel VBA: Copy multiple Column of a Table

Similar if you want to use .DataBodyRange

lstO.DataBodyRange.Offset(1, 0).Resize(18).Copy Destination:=Range("Z1")
5. ## Thread: Excel VBA: Copy multiple Column of a Table

by pgc01
Replies
2
Views
69

### Re: Excel VBA: Copy multiple Column of a Table

Hi
Welcome to the board

See if this helps:

Dim lstO As ListObject

Set lstO = ActiveSheet.ListObjects("MyTable")

by pgc01
Replies
5
Views
93

### Re: Issue scrolling on a spreadsheet

Hi

Mayb you have restricted the scroll area of the worksheet?

Try executing in vba:

ActiveSheet.ScrollArea = ""

or use the sheet properties dialog and clear the scrollarea (for ex. in...

by pgc01
Replies
4
Views
172

### Re: columns question

Hi

If you have a long list of columns, is there not some logic to it, like every 4th column, or columns that have a header starting with "MKT" or something that allows you to identify the ...
8. ## Thread: Filter only displays 10,000 unique items

by pgc01
Replies
1
Views
129

### Re: Filter only displays 10,000 unique items

Hi

You can't, not directly using the autofilter.

You can check MS support here:

https://support.microsoft.com/en-us/help/295971/not-all-items-are-displayed-in-the-autofilter-pivottable-list...
9. ## Thread: Search a string in one cell for an exact match of a value from another cell

by pgc01
Replies
3
Views
57

### Re: Search a string in one cell for an exact match of a value from another cell

You're welcome.
10. ## Thread: Search a string in one cell for an exact match of a value from another cell

by pgc01
Replies
3
Views
57

### Re: Search a string in one cell for an exact match of a value from another cell

Hi
Welcome to the board

Add the separator to the search

=ISNUMBER(SEARCH(";"&\$A2&";",";"&MachData!\$B\$2&";"))
11. ## Thread: [VBA] Adding string to each element in an Array

by pgc01
Replies
3
Views
120

### Re: [VBA] Adding string to each element in an Array

You're welcome. Thanks for the feedback.
12. ## Thread: [VBA] Adding string to each element in an Array

by pgc01
Replies
3
Views
120

### Re: [VBA] Adding string to each element in an Array

Hi
Welcome to the board

See if this helps:

Sub Test()
Dim v As Variant
Dim str1 As String, str2 As String

by pgc01
Replies
6
Views
235

### Re: Add shape to fixed position in a chart

You're welcome.

by pgc01
Replies
6
Views
235

### Re: Add shape to fixed position in a chart

Sorry, you may have noticed that there's a typo in the code

Where you see "ActiveSheet.Shapes..." should be "ws.Shapes..."

by pgc01
Replies
6
Views
235

### Re: Add shape to fixed position in a chart

Hi

This is an example.
Assuming you have in worksheet Sheet1 a chart object named MyChart, this example add a rectangle to the worksheet just under the chart object, with half witdth and height....
16. ## Thread: Macro for Random Time Generator with values greater/lesser than previous cells

by pgc01
Replies
3
Views
150

### Re: Macro for Random Time Generator with values greater/lesser than previous cells

Hi

Another option

In A1: =RAND()

In B1: ="6:0"+SMALL(\$A\$1:\$A\$3,ROWS(\$B\$1:B1))*("16:0"-"6:0")+((ROWS(\$B\$1:B1)-1)&":0")

Copy A:B down till row 3
17. ## Thread: numbers in sequence =IF(ROW()-ROW(A\$8)<=B3,ROW()-ROW(A\$8),"")

by pgc01
Replies
1
Views
112

### Re: numbers in sequence =IF(ROW()-ROW(A\$8)

Hi

In A9:
=IF(ROWS(\$A\$9:A9)<=\$G\$5,ROWS(\$A\$9:A9),"")

Copy down till A50
18. ## Thread: List all subs into listbox

by pgc01
Replies
5
Views
175

### Re: List all subs into listbox

Hi

To get the names of all subs in your project you have to use the VBA Extensibility Model

You can learn it here:

http://www.cpearson.com/Excel/vbe.aspx

Check in the page:

by pgc01
Replies
6
Views
235

### Re: Add shape to fixed position in a chart

Hi Hans
Welcome to the board

Sorry, not clear.
The title says "add shape in a chart" but then the text says "add a rectangle next to the chart"

Do you want
- to add a shape to the worksheet...
20. ## Thread: Protect Three Columns

by pgc01
Replies
3
Views
117

### Re: Protect Three Columns

Try:

Sub lockSomeCells()
Dim ws As Worksheet
Dim LastRow As Long

Set ws = Worksheets("Sheet1")
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
21. ## Thread: Protect Three Columns

by pgc01
Replies
3
Views
117

### Re: Protect Three Columns

Hi

You are locking the last cell in column C.
Don't you want to lock all the cells above too?
22. ## Thread: Apparently empty (blank) cells aren't empty

by pgc01
Replies
24
Views
12,473

### Re: Apparently empty (blank) cells aren't empty

Not very clear the first part of the Countifs()

COUNTIFS(BO\$3:BO\$4001,BO3,BO\$3:BO\$4001,"<>",...

both are testing BO3:BO4001?

Can't you take the second test out, like
...
23. ## Thread: Apparently empty (blank) cells aren't empty

by pgc01
Replies
24
Views
12,473

### Re: Apparently empty (blank) cells aren't empty

The cell is not empty, you said yourself it has a formula.

You forgot to post the formula that you are using.
24. ## Thread: Formule not refreshing

by pgc01
Replies
2
Views
185

### Re: Formule not refreshing

Hi

In the Edit Links dialog what's the option checked in the Startup prompt?
25. ## Thread: Dividing fractions into new cell

by pgc01
Replies
8
Views
213

### Re: Dividing fractions into new cell

For a text value I'd use:

=("0 "&A1)+0
Results 1 to 25 of 150
Page 1 of 6 1