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

x

Microsoft Excel

131K

Solutions

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.

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

Sign up to Post

Hello experts, I had this code created by Byundt exactly a year ago and need it updated.

Sub UpdateSheets()
    Dim ws As Worksheet
    Dim cel As Range, rgData As Range, rgHeaders As Range, Total As Range
    Dim i As Long, n As Long, nRows As Long, lRw As Long
    Application.ScreenUpdating = False
    With Worksheets("DataOnly_Sheet2")
        Set rgData = .Range("A2")
        Set rgData = Range(rgData, .Cells(.Rows.Count, rgData.Column).End(xlUp))
        Set rgData = Intersect(rgData.EntireRow, .UsedRange)
        Set rgHeaders = rgData.EntireColumn.Rows(1)
    End With
    n = rgData.Rows.Count

    For i = 1 To n
        Set cel = rgData.Cells(i, 1)
        If Left(cel.Value, 15) = "Establishment :" Then
            Set Total = Nothing
            On Error Resume Next
            Set Total = rgData.Columns(1).Find("Establishment Total", LookAt:=xlPart, MatchCase:=False, LookIn:=xlValues, after:=rgData.Cells(i, 1))
            If Not Total Is Nothing Then
                nRows = Total.Row - cel.Row + 1
                Set ws = Nothing
                Set ws = Worksheets(Mid(cel.Value, 17))     'Establishment :
                If ws Is Nothing Then
                    Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
                    ws.Name = Left(Mid(cel.Value, 17), 31)            'Establishment :
                    rgHeaders.Copy ws.Range("A1")
                End If
                With ws
                    ''/// wrap …
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

I have an excel spreadsheet that if something is selected in a dropdown in cell D10 then E11 will be disabled
0
I am sure this is a simple fix, but I cannot figure out how to get the scrolling window in the attached worksheet titled - Dashboard -  of the attached workbook to stop when it reaches the bottom of the referenced range.  Please see the attached screenshot also.

Thank you for your assistance!
Screenshot.docx
NCCU---Contracts-Inventory-Listing-.xlsm
0
I have recently created a quotation tool which opens up into outlook when the quote is built as a screenshot to send to a customer. I have tested it on my system and it works perfectly, yet I have sent it to a colleague who uses windows 10 (im on 8) and Office 2016 (im on 2013) and it does not work. It just says the location inst available. Please can someone help me with this. I have attached the excel document created.
Quotation-test.xlsm
0
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
0
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

0
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

0
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
0
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,
0
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.
0
Concerto Cloud for Software Providers & ISVs
LVL 4
Concerto Cloud for Software Providers & ISVs

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.

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

0
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
0
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).

Capture.JPG
0
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.
0
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.
0
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,
1
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.
1
please see attached.
E.xlsb
0
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.
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.

A.pngMatch-word-Flora1.xlsx
0
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
0
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

0
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 …
0
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
0
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.
0

Microsoft Excel

131K

Solutions

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.