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

x

Microsoft Excel

132K

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

What is the proper syntax (DAX) to compare two values and if there is a difference then return the newer value.

Similar to an Index Match. I am using Power Query to create a Pivot Table that will display the current value if match with new value and the new value if there is a difference.

=if (OrgNoA <>OrgNoB, then OrgNoB else OrgNoA.

This has to be in Power Query.

Thanks,

Karen
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 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
Hey Experts.  Odd issue being reported on four of my Windows 7 computers.  I deployed 20+ updates to several OU's on Saturday night.  On Monday, 4 computers out of 20 in one OU reported that when they open Word, Outlook or Excel, the QAT blinks repeatedly and makes the app unusable.  No other computers in any of the other OU's reported any problems.  3 of the computers are the same model (hp dc5750) and 1 is an HP 3130.

I compared the 4 experiencing an issue to a couple out of the 20 working computers to see if there was any glaring differences and even after checking for differences in Office/OS patches, or computer driver versions, nothing sticks out.  

So far I've:
  • run a repair on the Office suite
  • tried starting the various apps in safe mode
  • logging in with a different user account (including one with admin rights)
  • deploying additional Office 2016 patches to one of the computers so it was completely up to date

I'm out of ideas.  Any suggestions on where to go next or how to trouble-shoot further?  Thanks in advance experts!
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
I am trying to extract a long string value from a closed worksheet and ExecuteExcel4Macro returns Error 2015 - the string  is longer than 255 chars.

I  have switched to ADO and am having problems with the connection string, it generates an error

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\john\Documents\BSMA\Financials\OpenInvoices\2017-03\2017-03-29 243 Carnival UK.xls;Extended Properties="Excel 12.0;HDR=No;";

The Sub Routine Code itself is based on a sample I found on the Web

Public Sub GetCrewData(SourceFile As Variant, SourceSheet As String, _
                   SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)

    Dim rsCon As Object
    Dim rsData As Object
    Dim szConnect As String
    Dim szSQL As String
    Dim lCount As Long

    ' Create the connection string.
    If Header = False Then
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=No"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=No;"";"
        End If
    Else
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data 

Open in new window

0
Hi everyone!
I've encountered a problem of combining different databases together in a single query. I'm searching for the way to get a desired result, but no success so far..". Possibly someone could point me to a right solution.

Description:
  1. There are 2 databases (DB): DB1 - Visual FoxPro database and DB2 - MS Access database
  2. Quering databases is organized with ADO
  3. In DB1 I have all the necessary information except a list of sold items. Sold Items list is located in DB2
  4. I need to query DB1 and list all the models, which have not been sold yet
  5. All the queries must be performed from inside Excel and returned to excel as well

A sample of simplified SQL query:
[VEHICLES] - Table from DB1 (Visual FoxPro)
[SOLD] - Table from DB2 (MS Access)
"	SELECT [VEHICLES].VEH_MODELS FROM [VEHICLES] " & _
"	LEFT JOIN [SOLD].VEH_MODELS FROM [SOLD] " & _
"		ON [SOLD].CODE=[VEHICLES].CODE " & _
"	WHERE [SOLD].VEH_MODELS IS NULL"

Open in new window

Problem: How can I make ADO understand which table is in each DB?

Initially ADODB Connection.string is set to use Visual FoxPro driver and DB1 location
(Example: ADOcon.ConnectionString="Provider=vfpoledb;Data Source=" & DB_Path & ";Collating Sequence=machine;")

Possibly anyone has faced such problems previously and could suggest smth?
P.S. Please don't ask why data is in separate databases =))
0
Hi,

I have an Excel workbook that contains data in multiple tags.

I am trying to export to a PDF file but I know see the first page when I opened the PDF file.

Please advise how I can do this.

Thanks.
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
What does it mean to be "Always On"?
LVL 5
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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
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
If you look at the Orography tab in the attached file there is a graph. The blue line represents a hill or ridge. The dots represent the position of a scaffold on the hill. On the left is the upwind side and there is an orange line. If the scaffold is above this line then it is affected by the terrain and a set of equations need to be checked. I want to shade above the line. On the right is the downwind side. I want to shade below the grey line down to blue. I can introduce dummy nodes to place a node on the blue line directly under the grey line node. I can do the same the other side at 0,900. I cannot find how to shade these areas?
graph.xlsx
0
Dear Experts,

Please guide me on Following Formula in Countif. i have Range Range A1:A5 = "F" and B1:B5 = "M and Criteria is at C1 = "FM" i am Using Formula Countif(A1:A5&B1:B5,C1) ans must be 5 but not working with Single enter or Array Formula Ctrl+Shif+Enter Please guide how to figure it out.

Regards,
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 5
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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
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
I have a list of PST files in Excel (with their full path)

a VBA running within excel would  open each PST file (load it) into Outlook and then count the items in each folder and close it.
It would then write in a cell the information for the PST and proceed with the others, until exhaust the list.

At least for now,  I need just a hint on how access the current outlook session (I now how to start a new one from Excel, but it would much more efficient to use an already open instance of Outlook)
0
Error received from VB when I submit my codeSnippet of the linked Excel tables I need to update in AccessError received from VB when I submit my codeGood day to all! I am literally at wit's end with attempting to create a VBA code that works to update linked Excel linked tables in Access. Let me provide some background. Attached is a screenshot of just SOME of the linked Excel tables in Access that I need to create a new field for. There are a total of 40 Groups and at 11 SELECT tables per group, you do the math that is A LOT of tables to update manually (440= 11 x 40), that is not only cumbersome, but it is painful! There has to be a more automated way!

Naturally, I would have submitted an ALTER command with ADD Column, and be done with it! But as you may know, in Access 2002, MS took away the benefit to modify linked Excel tables. So that door closed on me.

I have turned to VBA. Not that great at it, SQL is more my thing, but apparently there is literature out there to perform such task, the one I am trying to do via VBA. And as I am getting more exposed to VBA, I am getting the gist of it. I believe I created a VBA code that will do the job (see below), but when I perform the VBA, I get  Run-time error '2147217900 (8004e14):
Syntax error in ALTER TABLE statement.
Now what am I doing wrong? Can someone please help? Further, once this problem is involved, can someone please show me how to do mass ALTER TABLE statements within VBA, please? That way I can get through this painstaking task? :-( In SQL, I would have been able to do the mass statements …
0
I need to extract data from a table on a web page but i don't know what to call it. I inherited a workbook which does this for another site and identifies the table as "1". Please take a look at this page and let me know if possible what to call the Activity Log table (about halfway down the page). Ideally I want to retrieve the data under PAST FLIGHTS.

"http://flightaware.com/live/flight/A6ETA"

.WebSelectionType = xlSpecifiedTables
.WebTables = "1"

Open in new window


Thanks!

John
0

Microsoft Excel

132K

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.