Replies

Berton,
64-bit
Thank you for tutorials to install Win7 within UEFI but I am not sure why since I am trying to install Win10 in MBR.

bbearren,
I do not know why the original OS or my method to change to GPT [3rd party software] is applicable to my problem .
GPT is not my issue, avoiding it is.

Restating problem:
I want to install Win10 in MBR.
BIOS set to boot both UEFI and Legacy
Boot order is Legacy first
Secure boot disabled
CSM enabled.
But Win10 installation UFD tells me I have an EFI system.
Boot menu does not offer me a “normal” or UEFI choice with USB
Only choice is USB which brings me back to “you have a EFI system.”
Where is my MBR option?

Win 7

Paul,
Less baggage, no additional partitions.
Do not need advantages of GPT
Boot manager does not work with GPT disks.
Curious as to why this is happening.
Stubborn.

bbearren,
Thanks for your reply. My system is running either UEFI/GPT or BIOS/MBR, not both and it does not change [unless it is schizophrenic], correct? If that is correct, I revert to my original conundrum.

Win10 installed and runs on my SSD with MBR which is verified with diskpart, see attached. But when I replace the SSD with a conventional HD [under 2T] Win10 at installation says I have EFI and need GPT, obviously not saying this when installing on the SSD.
Why does a different HD cause this? Is there anything associated with the HDs that would effect this? It is the only variable other than a more current version of Win10. Is this a MS Win thing?

The problem emails are HTML.
Default printer is same as before, a HP 870Cxi printer.

zeddy,
All looks good now, but 1 thing comes to mind. It would be very helpful if I can override the DIV/0 error in F when C=0.
F=(C/D)*G
I still want the error to return a string as is coded now, but I would then like to be able to over write this error return/string with a number since that is the intent of this column, as it is used for other “what if” calculations. I can fudge it by putting a 1 [or any number] in M so C is not 0 and therefore no error, but an over write ability in F would be more accurate and more eloquent.

zeddy,
In:
zFormula = “=SUMIF(M:M,TRIM(LEFT(A” And zRow & “,2)),N:N)”
Did you mean the “And” or did you want “&”?

I originally saw the “And” and wondered why but assumed you had a good reason. Had all sorts of weird issues until I exchanged “And” with “&”. Real head scratcher. If “And” was intended, then we will have to figure out what is awry.

In my sheet I limited changes below row22 to avoid issues.
If zRow > 22 Then Exit Sub
But for Columns M & N ONLY I would like to extend it to 35. Can this be done?

You explained why you used the variable “zformula =” for cosmetics and neatness, makes sense. Why then did you not use it throughout? You only used it for C.

zeddy,
I have spent a lot of time trying to modify your suggestions to work with my sheet. Finally got all to function as you and I intended.
At this point only 1 problem has arisen.

Formula for C uses A – C=SUMIF(M:M,Ax,N:N) which I also think is the best way to go.
My original formula used the specific string which was derived from A – SUMIF(M:M,”string from Ax”,N:N)
If the string is the same as Ax, then your formula/script for C would be fine.
But my Ax is essentially the same but includes a description of Ax preceeded/separated by a space.
Ax has 1 or 2 letters, space, description::
A [space] description for A
BB [space] description for BB

How would we modify
zFormula = “=SUMIF(M:M,A” & zRow & “,N:N)”
to use the letter before the space only, removing the space and description?
zFormula = “=SUMIF(M:M,A[before space]” & zRow & “,N:N)”
Basically the same idea as this, “I THINK…”
=LEFT(Ax,FIND(” “,Ax)-1)

Also:
Why did you use for:
Case [c1].Column:
zFormula = “=SUMIF(M:M,A” & zRow & “,N:N)”
Cells(zRow, “C”) = zFormula[/B][/COLOR]
instead of:
Cells(zRow, “C”) = “=SUMIF(M:M,A” & zRow & “,N:N)”
which was the format you used for the rest of the script. Why the difference?

zeddy,
Yes, in the “real world”, I do have C & D locked.
Can this be done using your script so C & D can change through the changing of M & N [SUMIF], but not by over-writing the cells in C & D? I think this will create less future hassles doing it this way versus “protecting” the worksheet.

I was surprised that deriving values for C through a formula [SUMIF] versus by direct inputting a value into cell would have any effect on a script that reads the value of C for another function. Still do not understand why that is.

zeddy,
Appears to work as needed.
After maud’s last revision, I thought that an automatic delete of N with delete of M was better than a text, which eventually needed to be deleted or overwritten. This was your approach, and for my needs, seems to works perfectly.
Thanks.

zeddy,
Too good to be true. Found a problem.
You have F change with change in C including a text return for a div/0 error, as requested. Works fine as is. But in my real sheet, C is a SUMIF from N.
=SUMIF(M:M,”A”,N:N)
When I change N, C changes. It would appear to me that your script should then respond the same as if I changed C directly, since F depends on the value of C.
But this is what occurs. If I change N so the SUM is 0, C changes to 0 [as it should] and F returns the error text as planned. But when I change N so its SUM is no longer 0 and therefore C no longer is 0, F does not update, the text remains. So C goes from 0 with error message in F as it should, to C having a value not 0 but F does not recalculate and leaves the error text.
I have to change C directly to refresh F as planned.
Can you help me with this?

Maud,
Thank you, works fine.
I see you added to original script which I received via email. This addition corrects my 1st issue.
But when I add a value to M then delete it, the 2nd part of the script adds the text to N and it cannot be deleted.

Code:

If Not Intersect(Target, Range(“M2:M100”)) Is Nothing Then
        Application.EnableEvents = False
        If Cells(Target.Row, 13) = “” Then Target.Offset(0, 1) = “need M”
        Application.EnableEvents = True

What I need is if no value exists in M I should be able to delete or change the value in N, whether it is the text returned from script or a manual entry, This was possible before the addendum of the 2nd part, though this is needed for the 1st issue which was an automatic refresh of N.

zeddy,
Double thanks. You are a true gentleman. I know you put a lot of time and effort into this. Greatly appreciated. This now has essentially all the functions I need. Unfortunately it is an ongoing project. For now we can consider it completed and closed. I have 1 more question but started a new thread.

zeddy,
All the potential possibilities or rules are numerous, so let me limit them to the probable.
“C” will never be all “0”s. Usually none and a maximum of only a few out of about 12+.
V.4 & v.5 work well except when you overwrite F since it is not replaced. Not sure if this is fixable because you do not always want the overwrite to be replaced. For now all seems ok.
But the previous request is not about these possibilities but only to show me how you would add the last code to v.4 [or v.5].

Code:

 
For Each c In Cells.SpecialCells(xlCellTypeFormulas, 16)
If c.Value = CVErr(xlErrDiv0) Then
c.Value = “no entry”
End If
Next

I chose v.4 because it was simpler and easier for me to see script technique. It would not do anything the “Named cell” approach would do, just a different way to do it as I see it. I am curious to see how it would work.

Zeddy,
How do I add your last error script

Code:

For Each c In Cells.SpecialCells(xlCellTypeFormulas, 16)
If c.Value = CVErr(xlErrDiv0) Then
c.Value = “no entry”
End If
Next

into your V.4?
Please create v.6 adding this script to your v.4.
Would this addition “break” anything in v.4?

Thanks.