Advertise Here

Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Microsoft Excel

131K

Solutions

202

Content

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Activating link on Intranet home page

I am working on a project which is Excel based but part of which links to another internal system, a Purchase Order creation platform.

I want to be able to open the PO Platform from the Excel sheet, ideally without going to the Intranet home page first.

Our Intranet home page has a button which links to the PO Platform and I can copy the link from it.

When I use that as a hyperlink in Excel it does not behave the same as when clicking it direct from the Intranet.

Using the link on the Intranet just takes you to the PO platform with single sign on authentication whereas using the hyperlink in Excel shows an error saying user hasn't logged off and then goes to the login window for the platform. I have spoken to our IT department and apparently it is to do with multiple logins for the PO platform being disabled and we do not want to re-enable it; they have tried in the past and it caused other issues.

So, I am thinking I can maybe set a macro that runs when the hyperlink is clicked. The macro would navigate to the Intranet home screen and "click" the button. The button is stored within a table on the home screen and has a specific jpg as a symbol.

Can this be done?

Thanks
Rob Henson

9 Comments

Question by

zack carter

5h agoSolved

Advice on Code Failing to function

eThe below code is meant to split row data that contains a special character "/" within the cell data for example "SEL/EHL" and populate single value into the row below by having row only for "SEL" and a duplicated row with the cell now with the "EHL" value and to remove the "/" from the metadata that was split.

However the code below for "&" works fine but the above fails to run and generates a run time 9 error message "Subscript out of range" message.

Can any of my peers advise why its happening on this "/" piece of code and not the "&" part of the code even though they are meant to be doing the same thing.

Option Explicit

Sub clean_pos_data()
    Dim rawData() As Variant
    Dim startRange As Range
    Dim v As Long
    Dim i As Long
    Dim j As Long
    Dim x As Long
    Dim cleanData() As Variant
    Dim splitField() As String
    
    With ws_PosSeq
        Set startRange = .Range("a3")
        rawData = startRange.Resize(.UsedRange.Rows.count - startRange.Row, .UsedRange.Columns.count).Value
    End With
    
    For i = 1 To UBound(rawData, 1)
        If InStr(1, rawData(i, 9), "&") > 0 Then
            splitField = Split(CStr(rawData(i, 9)), "&")
            For x = 0 To UBound(splitField)
                 v = v + 1
            Next x
        Else
            If InStr(1, rawData(i, 9), "&") > 0 Then
                splitField = Split(CStr(rawData(i, 9)), "&")
                For x = 0 To UBound(splitField)
                    v = v + 1

Open in new window

…

5 Comments

Question by

Farhan Mahmood

6h agoSolved

VBA Function Help_3

Dear Respectable Experts,

i need help below code attached function Extract a number from a string value and returns numbers as string i need that it returns number as value or number please help me if it is possible.

Thanks.

Option Explicit

Public Function ExtractNumbers(AValue As Variant) As String
 
  Dim Character As String
  Dim Index As Long
  Dim Result As String
  Dim Value As String
  
  Value = CStr(AValue)
  For Index = 1 To Len(Value)
    Character = Mid(Value, Index, 1)
    If IsNumeric(Character) Then
      Result = Result & Character
    End If
  Next Index

  ExtractNumbers = Result
 
End Function

Open in new window

9 Comments

Question by

APD_Toronto

15h agoSolved

Some data is Indented in Excel

Hi Experts,

If you will see under attached file, some data seems indented, but I have not used the indenting. Why is that?
Goals-Template.xlsm

1 Comment

IT just installed Microsoft Word 2016 on my computer, so now I have both Word 2013 and Word 2016. When I double click on a DOCX from within Windows Explorer it launches 2016 even though I:

Used Control Panel->Default Programs->Associate a file type or protocol with a program:

I associated both DOC and DOCX files with "C:\Program Files (x86)\Microsoft Office\Office15\WINWORD.EXE" which is Word 2013.

Any ideas on how to set a previous version of Word as the default?

Thanks in advance,

4 Comments

I want to use VBA to populate the cells in a single column with the following formula.

=IF(ISNUMBER(SEARCH("501020",A2)),"x","Y")

where A2 is the starting location of the text that I am searching for "501020", and C2 is the starting location of the result (x or Y). Each subsequent row would have different text in column A and the appropriate result in column C. The number of rows in the worksheet will vary ranging up to 500 rows.

I want to duplicate the same results that I get if I copied the above formula in C2, and pasted it down to the last row containing data in column A.

7 Comments

Concerto Cloud for Software Providers & ISVs

LVL 4

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 2

Question by

SiHodgy007

1d agoSolved

VBA pivot table looping help

Any idea how to loop through pivot tables applying conditional formatting instead of doing it manually as below.

     ActiveSheet.PivotTables("Test1").PivotSelect "", xData, True
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -11489280
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
      
         ActiveSheet.PivotTables("Test2").PivotSelect "", xData, True
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With

Open in new window

…

4 Comments

LVL 4

Question by

Stewart_HendersonNO1

1d ago

Dashboard in Excel

Hi,

I have a dashboard created but wish to link some of the information to the data behind it, for example, showing on the front sheet I wish to look at points 6-11
1) Overdue tickets - done
2) Open Tickets - done
3) Problem tickets - done
4) Completed tickets - done
5) Unassigned tickets - done
6) The person who has been assigned the tasks most often - support on this
7) Which user has reported the most tickets - support on this
8) How to check your ticket (based on the data in the IT Action List) - is there an easy was of a user clicking on a button which then displays the tickets they have reported
9) Top 5 categories reported (number of instances) - chart
10) Top 5 subcategories (number of instances) - chart
11) Open tickets by category - pie chart

Kind regards, Stewart
IT-Log.xlsx

33 Comments

LVL 18

Question by

Edward Pamias

1d ago

Excel - formula to create a Dynamic Chart

All,

I need a formula to make a chart dynamic. See attached. All I used is the grand total (column K) and Tech Names (column A).

5 Comments

Dear experts

I have the source document with vocabulary, meaning and its usage, each identified separately.
I need the help of the experts with a macro which can do the following onto a excel sheet:
Step1: Strip the word starting with bold and ending before the colon=>Put this in cell A1=> Example- abstruse (adjective):
Step2: Strip the words after the colon in step1 above but ending in the same line in cell B1=> difficult to understand; incomprehensible
Step3:Move to next line and copy entire stuff after step2 but before the next word beings in cell C1 example=> Physics textbooks can seem so abstruse to the uninitiated that readers feel as though they are looking at hieroglyphics.

Repeat the above steps by moving to the next word accolade (noun): and copying them in row 2 onwards.
The word document has 95 pages in it.
Each page starts with the logo (gif and the name below given in url). This detail need not be extracted.
I sincerely believe there is no copy right violation.
Kindly help.
Thank you

Extract from the page

abstruse (adjective): difficult to understand; incomprehensible
Physics textbooks can seem so abstruse to the uninitiated that readers feel as though they are looking at hieroglyphics.
accolade (noun): an award or praise granted as a special honor
Jean Paul-Sartre was not a fan of accolades, and as such, he refused to accept the Nobel Prize for
Literature in 1964.

15 Comments

Hi, How to remove few lines from text file before running the Macro. Once it got deleted before running the macro, I can schedule this in the Task scheduler. Please suggest.

Thanks
Venkatesh.

15 Comments

Question by

Rick Chase

1d ago

Excel Error

Hello,

Over the past week, I've had several users report that they are getting an error message whenever they try to open an excel document. The error is " There was a problem sending the command to the program." Users are only receiving this error when they try to open the document from the desktop. The file will load just fine when its opened from inside of Excel. I've combed through several forums online and most of them are suggesting to Ignore DDE, Disable add-ins, and Disable hardware acceleration. None of these methods have worked, neither has repairing office or resetting the file association. Is there anything else that I might be missing?

Thanks,

2 Comments

LVL 6

Question by

Flora

1d agoSolved

how to return unique count from two columns?

I had this question after viewing Extract Unique Values From Multiple Columns In Excel.

same spreadsheet, how can i get the number of unique count. somethign like sum(1/countif i could not get it work.

3 Comments

LVL 6

Question by

Flora

1d agoSolved

Extract Unique Values From Multiple Columns In Excel

please see attached.
E.xlsb

5 Comments

Hi, I have a couple of macros which needs to be run automatically every day. However after opening the sheet I need to move the tab and press CNTRL+D to download the file. HOw can do that automations. Please advise

Thanks
Venkatesh.

10 Comments

LVL 6

Question by

Flora

1d agoSolved

Formula by Ejgil and Neeraj modification help needed,

I had this question after viewing Excel Formula Lookup every word in single cell and match it with another cell if found the return the next column..

Ejgil and Neeraj helped me on the formula of lookup. but there is something which does not work now.

please see attached file. i would appreciate your help.

Match-word-Flora1.xlsx

3 Comments

Is it possible to insert image (location map ) in the sheet using VBA if I save all the respective images in a folder with Project code as name.

Say for example if I have DS101.jpg ,DS102.jpg (Project Code column in datasheet) saved in a folder. When I run the macro it fetches the respective image for each project and insert at a specified range (Location) in the template.

Please find the attached excel
CreateMultipleReport_v2a.xlsm
DS101.JPG

9 Comments

I had this question after viewing Conditionally format corresponding cell in another table.

How can I convert this VBA formatting to conditional formatting rules that incorporate the FormatCondition.SetFirstPriority method?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim oTbl_1 As ListObject, oTbl_2 As ListObject
    Dim rCL As Range
    Dim sFind As String

    Set oTbl_1 = Me.ListObjects("NW")
    Set oTbl_2 = Me.ListObjects("PF")

    If Not Intersect(Target, oTbl_1.DataBodyRange) Is Nothing Then
        oTbl_2.DataBodyRange.Interior.ColorIndex = xlNone
        oTbl_1.DataBodyRange.Interior.ColorIndex = xlNone
        sFind = oTbl_1.ListColumns(1).Range(Target.Row).Value
        Set rCL = oTbl_2.ListColumns(1).DataBodyRange.Find(sFind)
        If Not rCL Is Nothing Then Cells(rCL.Row, Target.Column).Interior.Color = RGB(184, 204, 228)
    ElseIf Not Intersect(Target, oTbl_2.DataBodyRange) Is Nothing Then
        oTbl_1.DataBodyRange.Interior.ColorIndex = xlNone
        oTbl_2.DataBodyRange.Interior.ColorIndex = xlNone
        sFind = oTbl_2.ListColumns(1).Range(Target.Row - oTbl_2.HeaderRowRange.Row + 1).Value
        Set rCL = oTbl_1.ListColumns(1).DataBodyRange.Find(sFind)
        If Not rCL Is Nothing Then Cells(rCL.Row, Target.Column).Interior.Color = RGB(184, 204, 228)
    End If
End Sub

Open in new window

3 Comments

The attached file contains the following code (and includes urls to sources for approach, and for retval = EmptyClipboard():

Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Declare Function EmptyClipboard Lib "user32" () As Long

Private Const VK_SNAPSHOT As Byte = 44
Private Const SW_SHOWMAXIMIZED = 3
Private Const VK_LCONTROL As Long = &HA2
Private Const VK_V = &H56
Private Const KEYEVENTF_KEYUP = &H2

Sub SampleShot()
Dim retval As Variant

retval = EmptyClipboard()

'~~> Take a snapshot and paste
Call keybd_event(VK_SNAPSHOT, 0, 0, 0)
ActiveSheet.Paste

retval = EmptyClipboard()
End Sub

PROBLEM:
1. The most obvious example is that, if I first select and save some text, and then I call the sub 'SampleShot' above, the text is pasted to the sheet, not the screenshot. When I run 'SampleShot' again, then the screenshot image is pasted to the active sheet.

2. I ran 'SampleShot' #1; then changed the layout of file windows on the PC; then I ran 'SampleShot' #2. After 'SampleShot' #2, the screenShot pasted did NOT show the PC screen layout that was created AFTER SampleShot #1, but the PC screen layout that existed BEFORE SampleShot #1.

I've tried to clear the clipboard (before and after screenshot), with no improvement (retval = EmptyClipboard()).

The two problem examples suggest that I am not clearing the …

4 Comments

Hi,

The following I use to get the recent MONDAY, I need this to be a Function call, to return the most recent Monday date.
=IF(WEEKDAY(TODAY(),2)=1,TODAY(),TODAY()-WEEKDAY(TODAY()-2))

This one I also need as a function, it would be called by say "GetWeek(CELL)"
Where the CELL (Is a date) replaces M10 in the formula below, and returns a week number 1-53.

=IFERROR(INT((DATEDIF((DATE(YEAR(M10),4,8)-WEEKDAY(DATE(YEAR(M10),4,6))-7), M10, "d") / 7))+1,INT((DATEDIF((DATE(YEAR(M10)-1,4,8)-WEEKDAY(DATE(YEAR(M10)-1,4,6))-7), M10, "d") / 7))+1)

Cheers

Microsoft Excel

23 Comments

Question by

Michael Hulse

1d ago

Excel VBA

I have an excel spreadsheet which I have put =RandomizeF in various cells to generate random alpha numeric data in. I want to add a button on the spreadsheet that when clicked would simply cause specified cells to refresh/reload the data in them.

3 Comments

First
Prev
1
2
3
4
5
6
7
Next
Last

Microsoft Excel

131K

Solutions

202

Content

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

COURSE of the MONTH

Microsoft Excel

Related Topics

Microsoft Excel

Related Topics