w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
  Home » EXCEL » Page 1
Very strange Find/Replace behaviour
I've imported the CSV to Range("A1"). Here's what I've found: $F$2 = 4 708,200 That value is not detected as a numeric. This is due to the CHR(160) existing in the 2nd place (the "space" after 4). If you want that value to become 4708200 (four million...), replace CHR(160) like you've done. This removes the comma because now excel detects those values as numerics. Since you haven't provided cor

Categories : Excel

inserting array formula into excel using vba and referencing cell value exported from outlook email?
Have you tried adding the line excWks4.Cells(intRow4, 4) = Trim(excWks4.Cells(intRow4, 4)) after the line l = excWks4.Cells(intRow4, 4).Address Alternatively you could may be able to add the TRIM function to your long formula around all references to those cells.

Categories : Excel

Mail excel rows to each person in a range
ASSUMES SCHOOL NAME IS COLUMN "E" This macro will copy each row into a new sheet which will be created and named as a school name using the school_name in column E. You would then have to e-mail each sheet to the respective school (which could be automated in a separate macro. EDIT: Fixed code and tested working: Sub SortKids() Dim CurRow As Long Dim LastRow As Long Dim NameTest As String, Na

Categories : Excel

Protected sheet creating hidden hyperlink
Index(array,,) Whatever cell references are in that array field even if you place them in an if will be opened up by Excel when the sheet is protected. A VBA solution to this problem is Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Locked Then Cancel = True End Sub This solution works because it only creates the secret hyperlink(which is not captu

Categories : Excel

Moving Columns using for loop
Sub MoveData2() Dim S As Integer Dim LastCol As LongSub MoveData2() Dim S As Integer Dim LastCol As Long Dim F As Long Dim NameTest As String, NameStr As String Dim LastRow As Long On Error Resume Next LastCol = Sheets("Master").Cells(1, Columns.Count).End(xlToLeft).Column For F = 4 To LastCol LastRow = Sheets("Master").Cells(Rows.Count, F).End(xlUp).Row Sheets("Master").Range(Cells(1

Categories : Excel

Refresh all pivot tables in excel spreadsheet when data changes
this code added to the worksheet containing the source data should work Private Sub Worksheet_Change(ByVal Target As Range) Dim Sheet As Worksheet, Pivot As PivotTable For Each Sheet In ThisWorkbook.Worksheets For Each Pivot In Sheet.PivotTables Pivot.RefreshTable Pivot.Update Next Next End Sub For more info check http://msdn.

Categories : Excel

VBA Copy Cells For i IF
I'd put the IF inside the For loop, not outside. If I understand correctly, you want to go through all the cells and if the condition matches, bring it over. What you're currently doing if the value at first cell matches, bring in the next 10000

Categories : Excel

Click event not firing when clicking on already selected item in VBA Excel ActiveX Combobox
I have been working on a problem exactly like the one you are stating for days and may have the ideal solution. The idea is to reset 'ComboBox1.Value' to the default value so that you can reselect the same option. This eliminates the need to click on a random item and then reselect the previous item in order to repopulate linked cells and requires no seperate reset button. Code: Sub cbReset() '

Categories : Excel

Excel 2013 VBA SelectionChange Event
I've been reviewing some of your code, my notes are below: I set up your code on my excel 2013 and ran it. (I added the named ranges as required). I reviewed the code by commenting out all code lines with LockWindowUpdate and with Application.ScreenUpdating and with Application.EnableEvents. The code works ok. However, when I re-added them the code did not work as you intended. When I enter

Categories : Excel

How to efficiently fetch an earlier row that is unique through two attributes?
One option could be to try a variation on the approach - without your dataset I can't test whether it is more efficient but I've run similar things on 1m+ row datasets without issue: = CALCULATE ( SUM ( [PERCENTAGE] ), FILTER ( Query, [ID] = EARLIER ( [ID] ) - 1 && [ACTIVITY] = EARLIER ( [ACTIVITY] ) ) ) Probably not what you want to hear but doing th

Categories : Excel

Excel Comparing Multiple Columns
I think you should try adding data into columnA (new) based on whether the (new) columnB values feature in the good list (elsewhere), say with MATCH. If they do not #N/A should be returned and that might be used to filter on and delete what is visible. If your 'good' list is in Sheet2 column C you might try: =MATCH(B1,Sheet2!C:C,0) copied down to suit.

Categories : Excel

Run Excel Function From Access VBA
Run takes the name of a routine to run, as a String. You will also need to include the code name of the worksheet in the call: x2.AutomationSecurity = msoAutomationSecurityLow x2.Run "Sheet1.CommandButton1_Click" for example.

Categories : Excel

Run Macro from prior date workbook
You can use Indirect to pull a reference to a created spreadsheet. For example, if the previous date is in A1, then a reference to the previous week data could be =INDIRECT("'" & A1 & " Dashboard'!B7) If you want excel to work out the previous date, then there is a formula you can use, using Cell and string slicing. Put this formula in a cell, and it will find out the current workbook d

Categories : Excel

Handle InternetExplorer object events in Excel VBA
Use Dim with the WithEvents keyword. From the documentation: Syntax Dim [WithEvents] varname[([subscripts])] [As [New] type] [, [WithEvents] varname[([subscripts])] [As [New] type]] . . . WithEvents: Optional. Keyword that specifies that varname is an object variable used to respond to events triggered by an ActiveX object. WithEvents is valid only in class modules. You can declare

Categories : Excel

Excel VBA still breaks after On Error handler
You could use On Error Resume Next, but you're better off encapsulating the error handling in its own Sub or Function. E.g. something like: Private Sub AddIfNotPresent(Coll As Collection, Value As Variant, Key As Variant) On Error Resume Next Coll.Add Item:=Value, Key:=Key End Sub which you could use as follows: Do While r.Value <> "" AddIfNotPresent uniqueValues, r.Value, r

Categories : Excel

Excel formula highest value with condition
What we need to do is make the PriceList less than the requested value when it doesn't match. The easiest way to do this is make the value Zero The 2nd table I put into A15:C19 First we need to construct the comparisons within the MAX formula: =MAX($A$2:$A$11*$B$2:$B$11=B16*$A$2:$A$11<=A16) next we need to convert all the TRUE/FALSE into 0/1 for multiplying: =MAX($A$2:$A$11*--($B$2:$B$11=

Categories : Excel

excel vba userform picture change on click
Your code must exist inside the userform. If the code is within a Module, it will not work. Also, ensure you have named the image controls to match the names given in your code: "set11"

Categories : Excel

Runtime error whle exporing range to pdf
The procedure below will export to the specified location a PDF: 'rng - The range you wish to be exported' 'strFP - The file path to save the PDF to' Public Sub fExportPDF(rng As Range, strFP As String) 'Export as PDF With rng .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFP, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=

Categories : Excel

Excel SUMIF variable date range
Because I think you need two conditions (one for each end of each week) I suggest SUMIFS: =SUMIFS(Expenses!A2:A1024,Expenses!B2:B1024,"<="&Main!C2,Expenses!B2:B1024,">"&Main!C2-7) This might be simplified if the results are in Main (eg next to the Week ending values, assumed to be in ColumnC) by not specifying that sheet. If obliged to resort to SUMIF you might add up to the

Categories : Excel

Export data of two tables into a single excel with two worksheets
You should use the AS_XLSX package created by Anton Scheffer (http://technology.amis.nl/tag/as_xlsx/). I am using it to generate various reports from the database. The package is free, compatible with Excel 2010, supported multi-sheets and formatting (and lot of more). sheers,

Categories : Excel

Check entered information in Column A with Column C. If it matches information in Column C then show information in the row next to that row
combining VLOOKUP with ISERROR First construct your VLOOKUP statement =VLOOKUP(A2,E:F,2,FALSE) Once you are happy that it is giving #N/A! and appopriate values in the correct place, we can wrap it in an IFERROR =IFERROR(VLOOKUP(A2,E:F,2,FALSE),"NO MATCH") The reason we wait to put the IFERROR in place is that it hides all errors, so if you've mistyped something, it's going to get hidden if

Categories : Excel

VBA EXCEL: Performing code on different workbook
Your DataManager and DateRegulator subroutines don't explicitly specify which workbook/sheet they're working on. That's fine as long as you realise they'll work on whichever book & sheet are active when they're called. So the first change I'd consider is With Workbooks(WName).Sheets(1) .Activate ' this makes sure the target workbook & worksheet are the active ones Even better migh

Categories : Excel

How to add a step function within a dictionary macro
Here's one approach (BTW, I wouldn't call a macro RUN): Sub ListUniques() Dim lastRow As Long Dim i As Long Dim dictionary As Object Dim vKeys Application.ScreenUpdating = False Set dictionary = CreateObject("scripting.dictionary") With Sheet14 lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For i = 3 To lastR

Categories : Excel

SUMPRODUCT, LEFT, MID and empty cells
If it is possible for you to add an additional column (say y), you can do something like this: W | X | Y 1 <blank> | | =if(isblank(w1), 0, if(len(w1)>2, if(left(w1,3)=="001", int(X1), 0), 0)) 2 <blank> | | ... copy down above... 3 001 | 2 4 <blank> | 5 001 | 5 6 002 | 6 7 <blank> | ... n

Categories : Excel

VBA EXCEL Setting print range based on date
If it is always the last 7 rows then Dim XLApp Dim XLWkb Set XLApp = CreateObject("Excel.Application") xlapp.visible = true xlapp.workbooks.open "C:UsersUserDesktopluboil.xlsx" Dim LastRow As Long Dim LastCol As Long LastRow = xlapp.activeworkbook.worksheets("sheet2").Range("A" & .Rows.Count).End(xlUp).Row LastCol = xlapp.activeworkbook.worksheets("sheet2").Cells(LastR

Categories : Excel

Excel move or copy with reference to another sheet in destination workbook
Surely the formula is =Notes!A1 and does not have the ! sign at the end. In any case, after the copy operation, the formula will be =[TemplateFileName]Notes!A1 Run a find/replace and replace the "[TemplateFileName]" with nothing. Make sure to look in formulas, not just values.

Categories : Excel

VBScript not pasting string values to the correct row in Excel
What ended up working for me was rather closing the Excel workbook after producing each report and then opening up the application and then exporting and formatting the workbook for the next person, saving and closing. Opening the workbook once, exporting, formatting, saving and closing the workbook and then looping to the next report while the application was open seemed to cause the strange exp

Categories : Excel

How can I reference a cell's value in a Power Query
This can be achieved using a named range and a custom function in PowerQuery: Name the cell you need to refer (type in a name into the file left of the formula bar) - e.g. SourceFile Insert a new blank PowerQuery query (PowerQuery ribbon -> From other sources) In the PowerQuery editor, go to View -> Advanced Editor and paste the following code; let GetValue=(rangeName) => let n

Categories : Excel

Excel 2010 formatting issue
The problem seems to have been unsuitable formatting (text rather than numeric) of some of the data being fed to formulae used for determining the extents of the data bars which therefore did not perform as expected. It seems the solution is a simple as converting from text to numeric some cells that display as digits. Although it seems there are many such cells it is quick and easy to do so all

Categories : Excel

Excel Search Function - Return value
I've taken your initial idea and tweaked it a little, you'll want to put the below into your cell C1 on sheet 1. =LEFT(RIGHT(B1,LEN(B1)-(SEARCH(Sheet2!$A$1:$A$3,B1)-1)),(FIND(" ",RIGHT(B1,LEN(B1)-(SEARCH(Sheet2!$A$1:$A$3,B1)-1)))-1)) It essentially trims the string around the word it finds. The SEARCH function finds the first letter, then the FIND function finds the first space after the word.

Categories : Excel

Copying specific columns conditionally to another worksheet
What you need to do is to do a For with a counter that will read all the cells with something in the sheet from up to down. F is the row of the new sheet where you want to place the stuff. Try something similar to this: sub alfa() UF = Cells(Rows.Count, 1).End(xlUp).Row for i = 1 to uf if sheetname.cells(i,Columnofyes).value = "YES" then sheetwheretocopy.cells(f,colu

Categories : Excel

Excel - Modifying Field Value Based on Newest Field Update
It sounds like your A column is calculating a running total as it is, if it increments by each sale and decrements for each cancellation, you would then just use this formula in cell A30 =INDEX(A1:A29, COUNTA(A1:A29), 1) You could simply put in each row the number of accounts sold i.e. 1, 2 etc. and for cancellations -1, then you would just need to use =SUM(A1:A29) in A30 to show the net va

Categories : Excel

How to call macro after Refresh or Refresh All button pressed?
You haven't actually connected the querytable to the class instance. Revised qtclass Option Explicit Private WithEvents qt As Excel.QueryTable Public Property Set HookedTable(q As Excel.QueryTable) Set qt = q End Property Private Sub qt_AfterRefresh(ByVal Success As Boolean) MsgBox "qt_AfterRefresh called sucessfully." If Success = True Then Call Module2.SlicePivTbl

Categories : Excel

VBA Macro not pasting correctly
Corrections (in my opinion) If wsCombined.Range("A3") > 0 Then Should become IF wsCombined.Range("A3").Value <> "" Then Don't use a custom function for the End function. It's redundant, and limits what you can do with it. wsCombined.Rows("3:" & LastRow(wsCombined)).ClearContents Should become wsCombined.Range("A3",WSCombined.Range("A1048576").End(xlup).Address).EntireRow.D

Categories : Excel

Excel Pivot table - get maximum for a period of 24 hours
Use real date time values in your hours column. Delete the rows with the day text. Instead, use a formula that increments from a starting date/time. For example: cell A2 contains the date and midnight time for Nov 17. Cell A3 and copied down contains the formula =A2+TIME(1,0,0) which increments by one hour. Now you con build a pivot table. Group by the date/time value by day and hour. Show t

Categories : Excel

Function to count distinct values in a column range
Sub CountDistinct() Dim RunSub As Long Dim LastRow As Long Dim CurRow As Long Dim Unique As Long LastRow = Range("A" & Rows.Count).End(xlUp).Row Unique = 1 For CurRow = 2 To LastRow If Range("A2:A" & CurRow - 1).Find(Range("A" & CurRow, LookIn:=xlValues)) Is Nothing Then Unique = Unique + 1 Else E

Categories : Excel

Evaluate manipulated SYSDATE in Pentaho output
The Parameter section of the PDI doesn't do any sort of query execution. If you have defined any value to a variable, there would be no change in the variable when you are passing to the job/transformation. In your case, you need to execute SYSDATE and SYSDATE-45 first using either "Table Input Step" and then define the variable, instead of directly assigning SYSDATE to the variable startDate/en

Categories : Excel

Excel get row that contains value
It sounds like you have 2 tables and would like a third. From your example, it looks like your desired table is effectively an additional column in your second table. The tags are largely a distraction. What needs to be done is: Given table 1 and a label, get the index. I can see two ways to approach this. OPTION 1: VBA User Defined Function add the following routine to a module in VBA Public

Categories : Excel

Filter data in one column based on data in other column
You can use a vlookup to find when an item matches another item in a different column. For your example, assuming that row 20 column F contains the item 52F233, use this formula for cell I20 =VLOOKUP(F20,$G$1:$H$1000,2,FALSE) This will put the unit price in cell I20 when a match is found and #N/A otherwise. You can paste this formula in the entire I column and it will show you when the match

Categories : Excel




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.