Go Premium for a chance to win a PS4. Enter to Win

x

Microsoft Office

61K

Solutions

40K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hello,

Is there a way to remove multiple hyperlinks from a Word document without changing any other formatting?

The attached Word file contains the first chapter of Genesis (KJV) copy/pasted from an .epub file as it is displayed in the File Preview window of Calibre e-book Editor (https://manual.calibre-ebook.com/edit.html).

The pasted text contains multiple hyperlinks, each of which is colored light blue and accompanied by a small superscript footnote marker as shown above the horizontal bar here:

2017-11-21a.png
The superscripts can easily be removed using the Find & Replace box (as shown below the bar), and of course, the font color is easy to change. However, making those changes obviously does not remove the hyperlinks.

Can the Find & Replace box also be used  in some way to remove the hyperlinks? If not, is there another way to do it aside from changing them one-at-a-time?

Thanks

Genesis-CHAPTER-1.docx
0
Free Tool: Port Scanner
LVL 11
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I got a series of questions and thank for some experts I managed to solve all of them.

But now I got a new requirement to calculate the "working days" not only limited to a particular month, but this time to allow cross month calculation.

Please check the sheet "Calendar (Expected)" for what I try to achieve.

Current:

Untitled.png
Expected:

Untitled1.png
Thank you.
Costs.xlsx
0
We are looking for ideas on getting Microsoft to defederate a domain.

We were looking to purchase Office 365 through GoDaddy. During that process, they asked if our domain pbhcpa.com had been federated previously to 365. Since we (here in IT) had made no conscious decision to do so, we said “no, it has not been federated.” We have three O365 accounts that are pbhcpa.onmicrosoft.com but pbhcpa.com was not affected by this. We purchased office 365 through GoDaddy. Our GoDaddy team got the message “pbhcpa.com has already been used to setup an Office 365 account at Microsoft. Before you can create any new users here, you must remove pbhcpa.com and any associated accounts from your previous Office 365 account.”

pbhcpa.com has already been used to setup an Office 365 account at Microsoft
We didn’t have any 365 accounts with pbhcpa.com so we opened a case with Office 365 support. The first tech found that someone at the firm, a year ago, created a Power BI account. The signup caused Office 365 to reserve our domain but never set it up as an actual tenant. This caused a shadow tenancy. The tech said this is a flaw on the Microsoft side and that he would have to escalate it. He had us log into our onmicrosoft account and run a Powershell script to defederate.

We connected to Exchange Online using https://technet.microsoft.com/library/jj984289.aspx

Then he had us run a modified version of this:

Set-Mailbox Identity -EmailAddress <NEW primary E-mail address>
Set-Mailbox identity -EmailAddresses @{Remove="<E-mail …
0
I had this question after viewing Modification of VBA to use named range instead of offset.

I have this great piece of code from Rgonzo1971.

while the solution, i have is perfect.  for my learning purposes. i wanted to know how can i change this so that instead of many OR and AND functions, i simply use an array for example lets say if i have more years to add then i simply use the  {2011, 2012, 2013}  for year like this ShD.Cells(C.Row, intYearCol).Value = {2011, 2012, 2013}   and for months to exclude ShD.Cells(C.Row, intMonthCol).Value <> {111, 114, 115}

is this something even possible?  because i have seen it in formulas but not in VBA.

Sub macro3()
intYearCol = Range("dataYear").Column
intMonthCol = Range("dataMonth").Column
intProductCol = Range("dataPRODUCT").Column
intAmountCol = Range("dataAMOUNT").Column
Set ShD = Sheets("Data")
For Each C In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))
    If (ShD.Cells(C.Row, intYearCol).Value = 2011 Or ShD.Cells(C.Row, intYearCol).Value = 2012) _
            And ShD.Cells(C.Row, intMonthCol).Value <> 111 And _
            ShD.Cells(C.Row, intProductCol).Value Like "[5-7]*" Then
        mySum = mySum + ShD.Cells(C.Row, intAmountCol).Value
    End If
Next
Sheets("Main").Range("B3") = mySum
End Sub

Open in new window

0
Please see attached file.  

I need help with a formula where when i put it in cell B2 and drag down and right. it will generate the YYYYMM dates for me as it is shown in the cells highlighted in yellow.

any help is appreciated.
EE6.xlsx
0
I had this question after viewing Modification of VBA to use named range instead of offset.

Rgonzo1971 was very kind of helping me many times.  the code  in earlier post was great. now i tried to implement this into my worksheet. it seems like for every cell, i have created two procedures for two cell. please see attached file. but i think there have to be a easiler way to combine all of these into one Sub Procesure. i do not know how to do this. any help is appreciated. if it would not be simplified then it seems like i have to have 16 seperate procedures for each of the cells.

EEE.pngEE.xlsb
0
excel vba
I have data i'm copying to another workbook, from my current workbook.

Dim curWks As Worksheet
Dim templWks As Worksheet
Dim rngToCopy As Range

  
Set curWks = ActiveSheet
With curWks

Set rngToCopy = .Range("A1:AX65453", .Range("a1").End(xlToRight).End(xlDown))
End With

Workbooks.Open _
fileName:="C:\Program Files\enterprise\Customer Copy\Customer_Template.xlsx"


If ActiveSheet.Name = "Project Data" Then
Else
Sheets("project Data").Activate
End If

Set templWks = ActiveSheet
templWks.Cells.Select
templWks.Cells.Clear

' APPENDS DATA
rngToCopy.Copy _
Destination:=templWks.Range("A65453").End(xlUp)


' after i copy data i need to insert a column at  Column P


Workbooks("Customer_Template.xlsx").Close SaveChanges:=True
Application.CutCopyMode = False

Open in new window



What I need:
after i copy data i need to insert a column at  Column P in the other workbook.
And give the Column Header a name "Customer  Price"


Thanks
fordraiders
0
OK...without going into all of the details of exactly what I'm doing:

I have a combobox on an Excel worksheet that lists the months January, February, March, etc.   The combobox references a range of cells (let's say B1 through B12) to be able to display the values to choose from.

I want to be able to do this without a macro.

If the month selected from the combobox is the current month or a future month, I want to display an error.

So, for example, right now we're in November.  If the user selects November, I want to display a message like "Invalid month" or something.   It's not the displaying of a message I'm having trouble with. It's how to get the program to determine that November is equal to the current month or that December (if that month was selected) is greater than November, etc.

In other words, if I was using numeric values for the months (January being "1", February being "2", etc), it would be easy to determine greater than, equal to, less than etc.   But since I'm using text for the months, I don't know how to make the comparison.

Thanks
0
I had this question after viewing How to get the same result given by formula vba Scripting Dictionary?.


Rgonzo1971 was so kind helping me, wrapping the formula into Evaluate method to output the result in a cell.

i used debug.print timer in the begging of my code and at the end, for many formulas like this. it takes quite a lot of time.

i was wondering if there is any easy solution with scripting dictionary to extract the same result but not using the formula.

if it is not possible or takes too much time, then i can live with this, otherwise if it is possible for scripting dictionary then it will really make the code faster.   for just one formula, i can replicate for my other formulas. all i need is just for this one formula.

I am not good at coding, but i found use of scripting dictinary very effective and fast.
for example,  VBA to remove duplicate with formula was too slow and with scripting dictionary was many time faster than formula method.

thanks very much for your help.
EE.xlsb
0
Hello.

Wondering if cell color can be controlled using a function instead of a macro.  Specifically what I need is to turn the cell fill color yellow when a data entry error is made by a user.  Here is my existing function which is in Cell C3:

=IF(SUM(B3-A3)>0,"Error","")

In addition to displaying "Error", which it already does, I want to modify the function to turn cell C3 yellow when an Error is made (i.e. when the value in B3 is greater than the value of A3)

Thank you
0
Concerto's Cloud Advisory Services
LVL 5
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

I had this question after viewing Vba to search in column B and if unique value is just one then return it to cell A1 of sheet1.


this code

gives error on .  if there one unqiue value in column B starting from B2 then it should return that unique value in Cell A1 of Sheet1

currenty it gives error.  runtime 13 sh1.Range("A1").Resize(UBound(aDict) + 1) = Application.Transpose(aDict)

Sub macro2()
Set objDict = CreateObject("Scripting.Dictionary")
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
Set Rng = Range(sh2.Range("B2"), sh2.Range("B" & Rows.Count).End(xlUp))
For Each c In Rng
    If WorksheetFunction.CountIf(Rng, c.Value) = 1 Then
        objDict.Add c.Value, c.Value
    End If
Next

aDict = objDict.Items
sh1.Range("A1").Resize(UBound(aDict) + 1) = Application.Transpose(aDict)
End Sub

Open in new window

0
how can I lookup with two values in Excel. Please read sheet 2 from sheet 1 in the attached example.
grid-map-analysis.xlsx
0
I need a macro that
1. moves the active cell up one row from the current location.
Tests for cell contents. Does this cell contain any numbers or alphanumeric characters?
if yes
     - move down one row and type the word "Filled"
     - move one column to the right
     - message box "Do you want to continue Yes/No"
          if yes, go to step 1 above
          if no exit procedure.

If no,
move down one row
Move one column to the right.
Exit procedure
0
Hi,
I am having problems trying to copy a range using the curly brackets.
One effort displayed the formula in text format.
I cannot seem to follow the website instructions and maybe someone can
walk me through it.
Many Thanks
Ian
0
Hi,
Is it possible to increase the font size on the formula bar without affecting the cells font size  ?
If so how ?
Thanks
Ian
0
how can I change the format of the attached sheet 1 to sheet 2

Regards,

Dallag
Change_Sheet1_Sheet2.xlsx
0
Rows 1 to 73  show what I am trying to do.

1. Move the contents  of the row below the call sign in Column B, or in Column C, (the columns are not regular) into Column O, on the the same row as the call sign from the row immediately above.
2 Where there is a suburb in column A, copy this name into each second row below, onto the same row as the call sign in column B, until there is another suburb.

I'll be able to tidy up the rest.

Thanks.
2017-TV-by-suburb-and-call-sign.xlsx
0
Column A contains the New ID number.
Column B contains the Old ID number.

In many cases, there are several New ID numbers for one repeated Old ID number.
And several repeated Old ID number for one New ID number.

There are a several long lists of Old ID from different files that need to be matched to New ID.

The concept is that a list of Old ID could be pasted into Column F, and  column E could repeat the same Old ID as often as it appears in Column B with the matching New ID from Column A, shown in column D - manual example shown in file.

Hope this makes sense.
Example-Old-ID-match-New-ID.xlsx
0
I ran Produkey64 and Belarc and got license keys for Office 2010 on my W7...
I installed W10 on top of W7 and W10 activated...BUT most everything was not working correctly...
W10 was messed up BUT it did activate...

Then I put in a NEW hd and did a fresh install of W10 and it activated...started re-installing all my applications...

Then I installed Office 2010 and using the key from my W7 PC it will NOT activate...
It will not activate on the internet nor by phone...I think phone activation on 2010 is not available...
I also have the key that is in the retail box and tried it...no joy...

Suggestions please...

If I go to change product key and enter my old key...I get "Sorry this isn't a Microsoft office 2010 ...you  may be entering a key from another version of office...

So...I uninstalled Office 2010 and tried another CD with a different key...and that will not activate either...
0
Ask an Anonymous Question!
LVL 11
Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Hello,

This is a follow-up question to a previous thread located here:

Auto-fill a cell's color based on numeric RGB values and vice versa in Excel

In that thread, the solution (by Ejgil Hedegaard) includes an attached Excel file in which the fill color of a particular cell, automatically changes in response to entering various values in three other cells labeled R (red), G (green), & B (blue).

This functionality is tremendously useful for me (and I suspect will be for others) because it enables you to instantly view the new color resulting from changes in any of the RGB parameters (ie without the necessity of the four clicks typically required to obtain that information):

        Home > Fill Color > More Colors > Custom

(The file also includes a chart for quickly converting from RGB decimal to RGB hex and vice versa.)

Additionally, the solution to the thread contains the formula for calculating Excel's color code:

        ExcelColor = R + G *256 + B *256^2

For example:

        Red = 204
        Green = 102
        Blue = 255

is calculated as follows:

        ExcelColor = 204 + (102*256) + (255*256^2)
        ExcelColor = 16737996

It also displays the following for reversing the process (ie :

        R = C Mod 256
        G = C \ 256 Mod 256
        B = C \ 256^2 Mod 256

Are these three formulas math or code and what is the purpose of the backslash?
0
Need OOO to work for my user accounts when they are disabled. How can I do this?

Running Exchange 2013 On Premise
0
hi experts,

 I have 5 dell computers that came with office 365 and the 30 days is about to expired. I need to purchase the 5 license but I don't want subscription. where or how can I buy the permanent licenses without having to uninstall and reinstall office 365?
0
I can open access fine using the following command line but, i cannot get the syntax for it to connect in excel through any of the wizards.  Any help would be appreciated-


"C:\Program Files\Microsoft Office 15\root\office15\msaccess.exe" "\\srv05\database.mdb" /wrkgrp "\\srv05\folder2\jobboss.mdw" /user username /pwd password123
0
Over the past year, I have purchased MS Visio 4 different times using Volume Licensing Services.

When I look in the Volume Licensing Services website, it shows that each of the keys has 50 activations available.

A couple of the purchases were for multiple users.

How do I track if I still have licenses available?, since my understanding is:

1) The same user can install MS Visio on both their laptop and desktop and only require one seat.
2) The totals are activations and not users. So If I purchase a new computer for a user, and install the same key on their new computer, it now shows as a 2nd activation even though only 1 license is required.

For example, one purchase was for 2 user licenses. I can see that the activation count is 2, but is that a laptop and desktop for the same user or 2 separate user installs.
1
I just placed a combobox that lists the 12 months of the year on an Excel worksheet.  The user is to select the applicable month.  Is there a way to reference the selection so that it appears in the middle header section when printing the worksheet?   Hopefully it can be referenced in some way without having to set up a macro.

Thanks
0

Microsoft Office

61K

Solutions

40K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.