We help IT Professionals succeed at work.

Microsoft Excel

137K

Solutions

38K

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.

Hi
In the following code the line     Set xlwb = xlAp.Workbooks.Open(strPath) is
causing issues. The code just seems to hang. I thought the error handler code
would have picked up any issues but it didn't. Can I add any further code or change it
to run more smoothly?
Thanks

Sub oCaller()

    Dim oSamePath As String: oSamePath = ActiveWorkbook.Path
    Dim strPath As String: strPath = oSamePath & "\" & "Freitan Props - TB - Feb 2020.XLSX"
    Dim strWorksheet As String: strWorksheet = "General Ledger Trial Balance"
    Dim oRowHeader As String: oRowHeader = "7050.00.00"
    Dim oColumnHeader As String: oColumnHeader = "CLOSING"
    Dim oRowHeaderColumnNumber As Integer: oRowHeaderColumnNumber = 6
    Dim oColumnHeaderRowNumber As Integer: oColumnHeaderRowNumber = 1
    Dim oValue As String
   
    oValue = GetIntersectValue(strPath, strWorksheet, oRowHeader, oColumnHeader, oRowHeaderColumnNumber, oColumnHeaderRowNumber)
   
    MsgBox oValue

End Sub


Function GetIntersectValue(strPath As String, strWorksheet As String, oRowHeader As String, oColumnHeader As String, _
Optional oRowHeaderColumnNumber As Integer = 1, Optional oColumnHeaderRowNumber As Integer = 1)

    On Error GoTo GetIntersectValue_error
   
    Debug.Print strPath

    Dim xlAp As Excel.Application
    Dim xlwb As Excel.Workbook
    Dim xlws As Excel.Worksheet
    Dim lngCol As Long
    Dim lngRow As Long
    Dim oNotFoundError As String: oNotFoundError = ""
   
 …
0
Experts,

How can I change the row  rCell2.Value = Date - Day(Date)  to return the end of the previous month if there is no data in the cell?
Right now, the code works as planned in all cases but returns a blank if the cell is blank.

   
 Range(Selection, Selection.End(xlDown)).Select
    Dim rCell2 As Range

    For Each rCell2 In Selection
        If IsDate(rCell2) And Not rCell2.HasFormula Then
             rCell2.Value = Date - Day(Date)
        End If
    Next rCell2

Open in new window



thank you very much
0
I am struggling with creating a dynamic bar chart that is comparing activity (Category) between visits (Aud & WAV). I have attached what I have come up with so far. I've tried the OFFSET formula method and INDEX-MATCH not to include blanks, but I cannot figure out how to get the y-axis (Category) to work dynamically.

Would appreciate the help.

Thank you in advance.
0
Hi

I am using the following custom function =PullValue(G2,G3,G4) in a cell of my Excel sheet but the result just shows #Value!
The idea is to pull a value from another workbook

Function PullValue(ByVal oSourceFileName As String, ByVal oSourceSheetName, ByVal oSourceCellRange As String) As String

        PullValue = "='[" & Range(oSourceFileName).Value & "]" & Range(oSourceSheetName).Value & "'!" & Range(oSourceCellRange).Value

End Function

1
0
Hi
I am using the following Excel VBA code to pull a value from a cell in a closed workbook.
How would I build one function that can be used in each cell to achieve the same thing?
Thanks


Sub oMain()


    Dim oSamePath As String: oSamePath = ActiveWorkbook.Path

    Call GetValuesFromAClosedWorkbook("G27", oSamePath, "Freitan Props - TB - Feb 2020.XLSX", _
            "General Ledger Trial Balance", "H35", False)
           

End Sub

Sub GetValuesFromAClosedWorkbook(ByVal oDestinationCellRange As String, ByVal oSourceFilePath As String, _
            ByVal oSourceFileName As String, ByVal oSourceSheetName, ByVal oSourceCellRange As String, ByVal blnConvertToValue As Boolean)
           
    Dim oFunction As String
           
    With ActiveSheet.Range(oDestinationCellRange)
        'Assigning the array formula to the specified range
        '.FormulaArray = "='" & oSourceFilePath & "\[" & oSourceFileName & "]" & oSourceSheetName & "'!" & cellRange
        oFunction = "='[" & oSourceFileName & "]" & oSourceSheetName & "'!" & oSourceCellRange
        Debug.Print oFunction
        .FormulaArray = oFunction
        'Removing formula from the cell and pasting the values in the cell
        If blnConvertToValue = True Then
            .Value = .Value 'Only activate this line if you want to convert the cell value
        End If
     
    End With

End Sub
0
Excel - I have a column where I am using military time -example: 0645

I have it formatted as Text but I have to constantly tell it to ignore the error to get rid of the little green flag in the corner.

Is there a way to not have the error flag show up all the time?
0
I have an Excel VB.net add in that I built in a previous version of Visual Studio. In Visual Studio 2019 where do I reference
Microsoft.Office.Interop.Word.Application?

Thanks
0
The following VBA solution from this forum has been working great. I just need to add the following criteria to stop the calculation from happening on blank rows:

If Column A = "" then Column J (Expiry Issue) = ""

The existing code is below. Additionally, I have attached a sample Excel file as well as a screen capture depicting what I am looking for.

Function expiryIssue(rng As Range) As String
Application.Volatile
Dim dte1 As Range
Dim dte2 As Range
Dim status As String
Set dte1 = rng.Cells(1, 1)
Set dte2 = rng.Cells(1, 2)
status = rng.Cells(1, 3)
Select Case Cells(rng.Row, "A")
    Case "AIP_ENR", "AIP_AD", "AIP_GEN"
        expiryIssue = ""
        Exit Function
End Select
If status <> "Expired" And status <> "Cancelled" And status <> "Replaced" Then
' If there is no Proposed or Approved expiry date
    If dte2 = "" And dte1 = "" Then
        expiryIssue = "Missing expiry dates"
    Else
    'If there is no Approved Expiry date and the Proposed Expiry date is within 60 days of todays date
        If dte2 = "" Then
            If dte1 < Date + 60 Then expiryIssue = "Review proposed expiry date"
        Else
        'If the Approved Expiry date is older than today's date
            If dte1 = "" Then
                If dte2 < Date Then expiryIssue = "Issue"
            Else
                If dte2 < Date Then expiryIssue = "Issue"
            End If
        End If
    End If
End If
End Function

Open in new window


Thanks!
Andrea
EE_Expiry_Add_Criteria_Blank_Rows.xlsm
Additional_criteria_Mar29.png
0
Experts,

I need VBA code to shade cells purple.
The condition is:
If column E = "Company A" or "Company B" then shade the cells in Column  K purple

How can I do that?
The conditional formatting does not work in this file.  
I think there could be some corruption so I want to test the formatting through VBA

Thank you
0
Hello Experts,

I have recorded a macro and I need to tweak it. but not sure how.
What I need to do is change the selected cells to end of previous month but the code is basing it off the value of what is in the cells.
I need the code to forget about what dates are in the cells and instead change the dates in the selected cells it to end of previous month based on current dates.
example:
if the cells contain 1/1/2020 I need them to change to end of previous month based on the current date of 3/28/20 which will change the cells to a value of 2/29/20 (leap year in this case).  The code below is changing the value to 12/31/19 (end of previous month based on the 1/1/20...the date that is in the cells. )

this is the part that I need to change:
 Range(Selection, Selection.End(xlDown)).Select
   
     Dim rCell As Range

    For Each rCell In Selection
        If IsDate(rCell) And Not rCell.HasFormula Then
            rCell.Value = WorksheetFunction.EoMonth(rCell.Value, -1)
        End If
    Next rCell

Below is the entire code:

Sub Closing_Banco_Change()
'
' Closing_Banco_Change Macro
'

'
    ActiveSheet.Range("$A$1:$AB$2211").AutoFilter Field:=7, Criteria1:= _
        "BANCO BOLIVARIANO"
    Range("B1320").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormulaR1C1 = "11/1/2018"
    Range("D1320").Select
    Range(Selection, Selection.End(xlDown)).Select
    
     Dim rCell As Range

    For Each rCell In 

Open in new window

0
Hi Experts!

Praying & Hoping u guys dealing with this pandemic with the best of spirits! Experts-Exchange has to stay up and running!  

I just got a great help from Tom in a PowerBI question and wanted to ask if PowerBI have some sort of templates that by setting up the data accordingly I can use said template? or PowerBI is just create & execute not re-use designs?

let me know please and thank u guys!
0
I need to loop through a folder of excel files and delete the drawing objects  on specified sheets within each workbook..  I have found the following code to delete
shapes on an active worksheet,

Sub DeleteShapes()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
Shp.Delete
Next Shp
End Sub

but I need to loop through an entire directory of excel files and delete the shapes on two specified named tabs. Each  file has the same two named tabs. Is there a way to  automate this process? I cannot get the workbook open Method to work without an error.
Thanks!
0
Excel - I need to be able to automatically sort by either Month or Location.  I was thinking a button for each sort.

Also I need it accommodate adding additional columns and rows. The user will be adding new columns and new rows on a continual basis and will need to sort as new information is added.

The first sort button should sort by  MONTH > START DATE > LOCATION

The second sort button should sort by LOCATION > MONTH > START DATE
Events.xlsx
0
I have a spreadsheet where I have multiple rows of different data where I would like to consolidate and summarize on a single line for each ID.  It would be great to have some sort of macro that can take the data from the source tab and generate the data in the output tab (see attached spreadsheet).
ee_help.xlsx
0
I am trying to compare two separate sheets in Excel.  The problem is, each row does not match to the adjacent row.  I want to select a row and compare it to another row displaying what is missing in in either sheet.
0
I want to populate two cells in a table as rows are added, The  first column is each row has a drop down which references all the staff records on the pr tab. e.g.if Staff number 33 is selected from the dropdown on the absence table, the name will then populate from the PR table.
Employee-PR.xlsx
0
Fix Win10 VBA Excel 365 loop / variable logic for conditional format.
If the row is a Total row and either (current month and current %, prior month and prior %, current ytd and current ytd%)  are higher than standard, highlight entire row
In test example line 34 should highlight, but none other

 I tried a few things with the conditional formatting variables and this compiles, but something is not working.
previous versions have highlighted every total row and the below one does not highlight any.
lastrow appears working, logic for is it is a total line  appears working.
cr (current row is probably working

Sub ConFormat()

Dim LastRow As Double
Dim myCell As Range

Dim cr As Long
' sv - startvar  sp startperc
Dim sv As Range
Dim sp As Range
' Set sv = Range("F2")
' Set sp = Range("G2")
Set sv = Sheets("Chk").Range("$F$2")
Set sp = Sheets("Chk").Range("$G$2")
cr = ActiveCell.Row
LastRow = Cells(Rows.Count, 3).End(xlUp).Row

For Each myCell In Range("C11:C" & LastRow)
    If Left(myCell.Value, 5) = "Total" Then
'   If OR(AND(ABS(F & cr)>sv,ABS(G & cr)>sp), AND(ABS(J & cr)>sv,ABS(K & cr)>sp), AND(ABS(O & cr)>sv,ABS(P & cr)>sp)) Then
' If (Abs(F & cr) > sv And Abs(G & cr) > sp) Or (Abs(J & cr) > sv And Abs(K & cr) > sp) Or (Abs(O & cr) > sv And Abs(P & cr) > sp) Then
     
      myCell.EntireRow.Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
 "=OR(AND(ABS(F & cr)>sv,ABS(G & cr)>sp), AND(ABS(J & cr)>sv,ABS(K & …
0
After running a macro to query a database that changes daily, I get two worksheets with formatted reports that may randomly vary between 10 and 15 rows each day.  

I would like to copy the rows from Sheet1, and paste it below the last row of data on Sheet2, leaving a blank row between the two reports.  I would add this code at the end of the macro to generate the merged report.
Example-of-Merged-Reports.xlsx
0
Determine the financial year based on a date column

Surface Date      Financial Year
30/06/2017      2016/2017
1/07/2017      2017/2018

The financial year goes from July 1 to June 30.
0
I have a table with about 5 million records.
I would like to use MS Excel's Get External Data to retrieve records for a specific day (only a few thousand records).
The challenge is that Get External Data wants to return the entire table.
How do I create a query with Get External Data and pass a date parameter so that only the records for that day are returned.

Thanks.
0
Hello again Experts,

Attached is sheet that contains more date but as a preview some lines are quoted below....
I have used Excels 'weekday' formula,  but I need if 1->7 (Monday through Sunday) then translate as "everyday", if 1->5 (Monday through Friday) then translate as '"Mon-Fri", where there's only 3 translae as Wednesday, etc.



SESSION_NAME      PROCESSING_DATE      day number      cycle name

ACDC0010      23-12-2019      1      
ACDC0010      24-12-2019      2      
ACDC0010      25-12-2019      3      
ACDC0010      26-12-2019      4      
ACDC0010      27-12-2019      5      
ACDC0010      28-12-2019      6      
ACDC0010      29-12-2019      7      everyday
ACDC0010      30-12-2019      1      
ACDC0010      31-12-2019      2      
ACDC0010      01-01-2020      3      
ACDC0010      02-01-2020      4      
ACDC0010      03-01-2020      5      
ACDC0010      04-01-2020      6      
ACDC0010      05-01-2020      7      everyday
ACDC0010      06-01-2020      1      
ACDC0010      07-01-2020      2      
ACDC0010      08-01-2020      3      
ACDC0010      09-01-2020      4      
ACDC0010      10-01-2020      5      
ACDC0010      11-01-2020      6      
ACDC0010      12-01-2020      7      everyday
ACTU0010      06-01-2020      1      
ACTU0010      07-01-2020      2      
ACTU0010      08-01-2020      3      
ACTU0010      09-01-2020      4      
ACTU0010      10-01-2020      5      mon-fri

I hope it's clear and not too hard to achieve this, it would be great help for me.
Thanks in advance
sesprocdate.xlsx
0
I have question regarding copying a value from excel cell. I know how to paste value or formula or formatting and so from one excel cell to another cell, that is not issue.

I want to know why I am getting different results  if I highlight only cell content :  text or number(I do not have formula in cell) and copy and  paste it to other location(it could be notepad) or if I click on cell( select it) and then do copy and paste it.
I have issue that sometimes when I select - click on cell( not just highlighting  content in) and copy it to other application not excel  I got different results.

Is it possible that clicking on cell instead of selecting(highlighting)text or particular number in the cell  I could copy some extra not visible values from that cell( e.g. line return I got this when I copy cell value in notepad ) or additional space after the end of text or  end of number in the cell .

How to avoid it?  I what to copy only visible content of cell?  Highlighting content every time then coping is time consuming. Is there other way to do that and to be sure that I copy only visible content of cell (letters of numbers no line return)?
0
With the help of you experts I solved my last question 29175699 on Bilinear interpolation on missing zvalues excel.

Pure reasoning makes me think it would be possible to take an other element out of the equation.
Would it be possible to get the y value through the input of a z value and x value.

My mathematical skills are near to none. How to go about to make this work. Any suggestion to get me started are welcome.
0
I have account number in this format 1-50101-007 say in cell a1 in cell c1 I enter the formula =right(a1,3) to get 007 what would I enter in cell b1 to get just 50101?
0
Hi,

Any expert out there experience in working Microsoft PowerBI?  I am searching for PowerBi videos or tutorials that are straight forward minimal talk.  Something that would show me the essence of the tool and maybe another small vid of how-to create a dashboard.
0