Posts by Report_2

Solved

Thank you Richie(UK) and Helmekki,

The workbook was digitally signed by me on the Office 2K machine that I no longer have access to.

The workbook was not set up as being a shared workbook.

However, I did make it into a shared workbook. I received an error stating that I couldn't share it because it contained macros. Clicking the OK button saves the workbook anyway.
I then removed the Share (that it alledgedly couldn't have) and sure enough I can View, Edit and Step into it's macros.

Thank you very much.

Excel 97 and I believe I created the Workbook using Excel 2000.
I used File Save As in Excel 97 just to be sure.

The workbook does not show as Read Only in it's Properties page.

I open the workbook. I am prompted to Enable Macros and I select Enable.

I hit the Run Macros button or Alt+F11 where I can view the attached macros but the Edit adn Step Into buttons are greyed out.

I tried going in through Alt+F11 and Project Explorer and recieve the message that the macros are unviewable.

Does anybody know why I can not view or edit ot step into the macros on this workbook and does anyone know if there is a way around it?

Thanks in advance.

http://ozgrid.com/forum/showth…ghlight=combine+workbooks

Yyama8 posted a question but the question is unreadable by me due to the browser translating the code unilke it was originally intended.

I realize I have not been here lately and I did look in Search for an answer.
I realize that the site has changed quite a bit since my previous visits.
I also do not know the appropriate forum for this question.

I ask because I referred someone to one of the threads
http://ozgrid.com/forum/showth…ghlight=combine+workbooks but see a lot of smiley faces in the code.

Is the problem unique to me?

Thank you Seti and Shades for finding the problem with my formula. I was blind to it.

I corrected the formula and copied it down A:A. I found it slow to calculate although functioning correctly.

I tried =COUNTIF(($A2=Regs)*1)>1 but it produced the error of too few parameters.

I created a new sheet with the same data and just used the
=COUNTIF(A:A,A2)>1
as the conditional format without defining the Range and it is much simpler but also slow to calculate.
I tried just applying it to 200 rows and still was somewhat slow. I was surprised.

Being slow is being just a minor setback is, in fact, very workable.

I knew it could be done but couldn't figure out how.

Thank you all again,
Bryan
(Report_2)

Edit>>>After I closed all sheets besides a single sheet it works quite fast.
Thank you very much.

Derk, Thank you very much for your information.
I apologize for the delay in response. I had been looking for the replies but didn't relaize it was on page 2 of the thread.

I added the string to the Refers to box and it does work a little bit?

In the attachment I added two duplicate numbers: one in A14 and another in A15.
As you can see A14 does not change the formatting color and does change the cell below the cell that it is duplicating.

In A15 the entry is changed to Red and again the cell below the entry that it is duplicating is also Red and not the entry it is duplicating.

Can/will you please troubleshoot the error for me.

Quote from thomach

I'd suggest using a Named Range. Assuming you have a column header for column A:

INSERT > NAME > DEFINE > enter a name (e.g., ColA) in the top box and the following in the "Refers to" box:

=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)

Then select the cells (in column A or more) for which you want the Conditional Formatting to apply (probably selecting a lot more rows than are currently filled if you add new data each day). Then:

FORMAT > CONDITIONAL FORMATTING > select "Formula is" from the dropdown and then in the next box enter:

=SUMPRODUCT(($A2=ColA)*1)>1

and set your formatting as desired.

Display More

I changed Sheet2 to Sheet1 and it worked very good. It also formats the original number that is now being duplicated.

If it wasn't for this forum board I would think myself an Excel Expert but after coming here I am thinking Excel idiot.
I had no idea that you could input a formula into the "Refers to" box. What makes it worse is that I don't understand that same expression in terms of words. (Maybe I a good night's sleep would help)

The data in this actual worksheet is vehicle registration numbers that do contain letters.
"=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))"
Does the above go in the named Range Refers to box or the conditional format box? (If I am going to be an idiot then I may as well go with it).
I assume Refers to box and if so would the conditional format formula need to be adjusted?

Thank you all in the mean time for your valuable input.

I have not visited since November and see the site has changed some. When I perform a search many of the results appear to be in HTML code instead of text so I did not find an answer to my problem.

Without using VBA code in Excel 8 (Office 97). I have a single column of numbers (Col A) and each day I need to add more numbers at the bottom of the list but I want to be sure that I have no duplicate numbers while I am entering them. I need it to look in all of the cells above the Active cell. (A1 is the heading="Regs")

I figure Conditional formatting would do the trick. I can not figure the correct way (if it is possible to use) to input an OFFSET within a range for example:

Cell Value, Is Equal to, =$A$2:OFFSET(A3,-1,0)
Meaning, If the value of A2 is equal to the value of A2:A2 then format.
or
Formula is, =$A$2:OFFSET(A3,-1,0)
or
Formula is, =IF(A3=$A$2:OFFSET(A3,-1,0))

The above will read A2 but not any further down if I use that same formula in A4, A5, A6...etc. as though it is not seeing the second part ofthe defined Range of cells.

So, for example, if my active cell is A50 then my inoperable Conditional formatting is:
Cell Value, Is Equal to, =$A$2:OFFSET(A50,-1,0) so it should look in A2:A49 and if the matching value is found then format the cell (A50).

In all of the above examples the ecell will format if the value matches the value found in A2 and no others.

Can the OFFSET be used within a formula when defining a Range of cells?
If so, what am I missing?

Then there is ASAP Utilities that offers "Flip selected cells" under Range as one of it's fine features.

Derk, Very nice macro. :biggrin:
Thanks.

Much cleaner than my 138 pieces and goes to the bottom without a bug.

I got to reading all over your site and see that using a loop is not the most desireable.

Thanks for coming through for me Dave.

Before I came to OzGrid I was the office Excel king (I still am) but now I realize I know nothing. But I am learning thanks to mostly OzGrid.

Thanks again.
:biggrin:

I recorded the following macro and it works. I am posting a portion of it though as it just keeps repeating what you see 138 times.

Sub InsertRows()
'
' InsertRows Macro
' Macro recorded 06/28/2003 by Bryan
'

'
Range("A4").Select
Selection.End(xlDown).Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlDown).Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlDown).Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.End(xlDown).Select
Selection.EntireRow.Insert

Can someone tell me how to make it loop as I have not yet learned how? I am working on it but am waiting for the IT department at work to install the VB help files that is the next lesson I am on.

Thank you Dave for replying.

No, just need to insert the rows at each item in column A.

The information That I add comes from the forecast meetings that should happen at the begining of the month but like this month it took place on 19Jun. So it is unpredictable as to which date I need to add the information to the inserted rows.

What I mean by automate is instead of selecting each target row manually for the entire sheet (for 7 sheets) and then Right click, Insert Row; I am hoping a click of a button or run a macro that I do not know how to write.

I can use:
Sub InsertRows()
'
' InsertRows Macro
' Macro recorded 06/28/2003 by Bryan
'

'
Range("A4").Select
Selection.End(xlDown).Select
Selection.EntireRow.Insert
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.EntireRow.Insert
Range("A14").Select
End Sub

This recorded macro ends at A14 and I do not know how to make it loop to the bottom of the sheet's data and then stop inserting when it gets to the last item on an individual sheet.

Thanks again.

The attached is a sample of the data I am working with. The actual workbook contains 7 sheets with a total of <500 different items.

The worksheet is used to formulate the forecast for our items.

Each month I need to add/insert a new row after each item so each month an additional new row is inserted manually.

Can this process be automated with a macro.

I tried the attachment on http://ozgrid.com/forum/viewthread.php?tid=3561 but that did not work for this example.

I appreciate your time and attention.

Bryan

Are any add-ins selected?
If so then deselect all of them. If the problem goes away then reselect each individually as needed or until you receive that error message again.

Are you sure there are no add-ins that begin with the letter 'G' such as 'Gxxx Wizard'?

HTH

Try going to Tools, Add-ins and remove the check next to that add-in. Click Apply, Ok.

Here is what he is looking to accomplish.

I do not have the Excel to HTML plugin.
I can attach a (two) .jpgs of what WillR is looking for.

In Office 2K I sign all of my Macros so I do not get the Macro warning message. I can send them to my peers and they have the option to Allow macros signed by me to run without the warning.

I created the signature within VBA Tools menu.

From another thread someone had recommended
http://www.ozgrid.com/Services/ChartAssScreen.htm

It is awesome and does exactly what I needed. It may be the best $30 our company has spent in a while.

Andy, Thank you for your time and effort.
Apparently the concept is presently over my head.

I would not be surprised if this has been accomplished by someone, somewhere because this task must be performed by many besides myself.

It has that $50 feel to it.

Data to charts. If I had any idea I would publish it.

Thanks again.