Posts by JohnDrew

Re: Insert comment on a cell with another cell value

I get an yellow highlighted error on .addcomment.
I have numbers in column X

I am wanting to add the Cell Value of cell X1 to A1 as a comment
Then continue down column A and post values which correspond: (A2 comment=X2 value)
Continue until it reaches the last value in Column X

Re: Copy Formula Down or Macro to Dup Row, move Data from previous row,

I stand and applaud your expertise. That's what I was wanting.

[TABLE="width: 192"]

[tr]

[TD="class: xl63, width: 64, bgcolor: transparent"]I need to copy the format for row E but it gives me the wrong information.
I need to insert a row to duplicate if column A changes and put the data from column D in Column C
DOOR
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] Qty[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Width[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Height[/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]A[/TD]
[TD="class: xl63, bgcolor: transparent"]2[/TD]
[TD="class: xl63, bgcolor: transparent"]10[/TD]
[TD="class: xl63, bgcolor: transparent"]11[/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]B[/TD]
[TD="class: xl63, bgcolor: transparent"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]12[/TD]
[TD="class: xl63, bgcolor: transparent"]13[/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]C[/TD]
[TD="class: xl63, bgcolor: transparent"]6[/TD]
[TD="class: xl63, bgcolor: transparent"]14[/TD]
[TD="class: xl63, bgcolor: transparent"]15[/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]D[/TD]
[TD="class: xl63, bgcolor: transparent"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]16[/TD]
[TD="class: xl63, bgcolor: transparent"]17[/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]E[/TD]
[TD="class: xl63, bgcolor: transparent"]2[/TD]
[TD="class: xl63, bgcolor: transparent"]10[/TD]
[TD="class: xl63, bgcolor: transparent"]20[/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: xl64, bgcolor: transparent"]What I need[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]Door Nbr[/TD]
[TD="class: xl63, bgcolor: transparent"]Qty[/TD]
[TD="class: xl63, bgcolor: transparent"]Length[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]A[/TD]
[TD="class: xl63, bgcolor: transparent"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]10[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]A[/TD]
[TD="class: xl63, bgcolor: transparent"]4[/TD]
[TD="class: xl63, bgcolor: transparent"]11forum.ozgrid.com/index.php?attachment/59481/[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]B[/TD]
[TD="class: xl63, bgcolor: transparent"]8[/TD]
[TD="class: xl63, bgcolor: transparent"]12[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]B[/TD]
[TD="class: xl63, bgcolor: transparent"]8[/TD]
[TD="class: xl63, bgcolor: transparent"]13[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]C[/TD]
[TD="class: xl63, bgcolor: transparent"]12[/TD]
[TD="class: xl63, bgcolor: transparent"]14[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]C[/TD]
[TD="class: xl63, bgcolor: transparent"]12[/TD]
[TD="class: xl63, bgcolor: transparent"]15[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]D[/TD]
[TD="class: xl63, bgcolor: transparent"]8[/TD]
[TD="class: xl63, bgcolor: transparent"]16[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]

[/tr]

[tr]

[TD="class: xl63, bgcolor: transparent"]D[/TD]
[TD="class: xl63, bgcolor: transparent"]8[/TD]
[TD="class: xl63, bgcolor: transparent"]17[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]

[/tr]

[/TABLE]

Re: Lookup two values in a Data Range

Thanks

I have two values I need to find in a chart.

(See Attached)

I can do a Vertical Lookup but don't know how to search for the second value to identify how much to offset to retrieve the value.

Re: Search for Cell Value and Draw Borders

I stand and applaud your expertise. I apologize I should have sent the real desired results.

See attached. Cell E2 will always be the top left corner.

If a "X" appears in Column A , that row will not have dividers

The Row Height and Column Width are determined by Column D and Row 2

The cabinets could be 1-5 units but still is dependant on values in Row 2. So the right hand column could easily change to a higher Alphebetic letter.
Also the if more drawers are needed the rows will reflect this making the bottom row change to a higher number.

Re: Search for Cell Value and Draw Borders

Is there a way to border with a Bold Thick Line?

I would like to find in Column "A" an "X"
A "Z" will identify the last used column
Then offset 1 column from where the "x" was found and the right side of the border will be the Column where the "Z" is located
Draw border
Search for more x's

Re: Remove inside BORDERS in a Row if a Cell in that Row has a value.

Yes,

The macro-recorder works for one given drawing with the exact borders, thus the recorded macro will not work.
Another macro now adjust the cell width and height depending on the values in ROW 1 and Column "D"
This makes it easy to adjust to any number of openings length and height.
For example if J1 and K1 were blank, it would only make two openings and redraw two openings versus the three as displayed in the attachment.

Thus the recorded macro for erasing the inside borders will not work since the macro uses fixed cell addresses.

It needs to:
Search Column "B" for "X", move to the right to Column "E"
Since there isn't any values inside the drawings, I will put a "Z" , one cell to the right and one down to identify the last used COLUMN and ROW
In drawing it would be "L7"

It now knows the last COLUMN which is "L" back one which will be "K"
The border will be for range E2.K2 for only the outside borders, no inside dividers.
Then search for next "X" and stop at ROW 7 which was identified with the "Z" in "L7" as the last used cell.
I am 70 and don't know much about the cyberspace, so if what I said did not make sense it's because I am still trying to learn that Binary (0,1) system they were teaching 20 years ago.
Note: I forgot to put the "Z" in cell "L7"

I would like to remove the inside borders from a row if there is an "X" in Column "B"
Column "E", on the left will always be the same. The right Column will change.

See Attached: The one on the right is what I am trying to achieve.

Re: Set Column WIDTH and HEIGHT to values in cells

I did not realize that the row height dimension isn't the same as row width. I need to multiply the row height to get it in the same relationship as the row width.
Example:

If row width is 20, I want the row height to give me a square when I apply a border. It looks like the row height needs to be multiplied by somewhere between 5 and 6.

I am wanting to put a "1" in ROW 2, starting with COLUMN B until the sum reaches the value in cell A3.

Example: I have 10 in A3, I would like the macro to place a "1" in B2-K2.

I would like to find the value in cell A1 in Range "Data" and replace with the cell Contents in A2.
The Data Range is B1. Z20, I have "x" in A1 and 2 in A2 and would like to replace all x's as identified in A1 with the value in A2 which is 2.

Re: Borders Macro don't Line up

I found the problem. Thanks

I am wanting to set the column width based on the values in row 2, and the height based on values in Column A

WIDTH Example B2=2, C2=10, D2=2. This would create WIDTHS of : Col B=2, Col C=10, Col D=2, Stop when the value is less than 1
HEIGHT Example A3 (which is ROW 3) value=2, A4=5, A5=2, A6=5, A7=2, A8=18, A9=2. Stop when the value in Column A is less than 1

Re: Find the last used column with border to set print area

Thanks, I will try a different approach on the print area.

I need to set the print area using the last used colulmn which has a border. It changes depending on the number of columns. Also would like the magnification to increase to fill one page. see attached

The attached file works with 3 Drawers and 3 Doors but if I use 2 Drawers and 2 Doors the Borders do not line up. I think the Drawers are right the Doors appears to be the problem.
Same thing happens with 3 drawers and 4 Doors. It has and extra border on the right side. See Attachedozgrid cabinet error.xlsm Also I would like for the Measurments to be in the cell to the right of the left hand border and center across each of the sections.

Change B3 and B4 to 2 then click draw. 3 and 3 work 3 and 4 don't but 4 and 4 does.??? See attachment

Re: Borders Outline using Macro don't line up

Sorry wrong Forum

Reposted: http://www.ozgrid.com/forum/showthread.php?t=185559