|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
|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.
|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:
Dim CurRow As Long
Dim LastRow As Long
Dim NameTest As String, Na
|Protected sheet creating hidden hyperlink|
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
If Target.Locked Then Cancel = True
This solution works because it only creates the secret hyperlink(which
is not captu
|Moving Columns using for loop|
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,
For F = 4 To LastCol
LastRow = Sheets("Master").Cells(Rows.Count, F).End(xlUp).Row
|Refresh all pivot tables in excel spreadsheet when data changes|
this code added to the worksheet containing the source data should
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
For more info check
|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
|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.
|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
|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:
SUM ( [PERCENTAGE] ),
[ID] = EARLIER ( [ID] ) - 1
&& [ACTIVITY] = EARLIER ( [ACTIVITY] )
Probably not what you want to hear but doing th
|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:
copied down to suit.
|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
|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
|Handle InternetExplorer object events in Excel VBA|
Use Dim with the WithEvents keyword.
From the documentation:
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
|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.
Private Sub AddIfNotPresent(Coll As Collection, Value As Variant, Key
On Error Resume Next
Coll.Add Item:=Value, Key:=Key
which you could use as follows:
Do While r.Value <> ""
AddIfNotPresent uniqueValues, r.Value, r
|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
The 2nd table I put into A15:C19
First we need to construct the comparisons within the MAX formula:
next we need to convert all the TRUE/FALSE into 0/1 for multiplying:
|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"
|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
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFP, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
|Excel SUMIF variable date range|
Because I think you need two conditions (one for each end of each
week) I suggest SUMIFS:
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
If obliged to resort to SUMIF you might add up to the
|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
|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
Once you are happy that it is giving #N/A! and appopriate values in
the correct place, we can wrap it in an IFERROR
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
|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
.Activate ' this makes sure the target workbook & worksheet
are the active ones
Even better migh
|How to add a step function within a dictionary macro|
Here's one approach (BTW, I wouldn't call a macro RUN):
Dim lastRow As Long
Dim i As Long
Dim dictionary As Object
Application.ScreenUpdating = False
Set dictionary = CreateObject("scripting.dictionary")
lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = 3 To lastR
|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,
int(X1), 0), 0))
2 <blank> | | ... copy down above...
3 001 | 2
4 <blank> |
5 001 | 5
6 002 | 6
7 <blank> |
|VBA EXCEL Setting print range based on date|
If it is always the last 7 rows then
Set XLApp = CreateObject("Excel.Application")
xlapp.visible = true
Dim LastRow As Long
Dim LastCol As Long
LastRow = xlapp.activeworkbook.worksheets("sheet2").Range("A"
|Excel move or copy with reference to another sheet in destination workbook|
Surely the formula is
and does not have the ! sign at the end. In any case, after the copy
operation, the formula will be
Run a find/replace and replace the "[TemplateFileName]" with nothing.
Make sure to look in formulas, not just values.
|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
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
|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
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
In the PowerQuery editor, go to View -> Advanced Editor and paste the
let GetValue=(rangeName) =>
|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
|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.
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.
|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:
UF = Cells(Rows.Count, 1).End(xlUp).Row
for i = 1 to uf
if sheetname.cells(i,Columnofyes).value = "YES" then
|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
in A30 to show the net va
|How to call macro after Refresh or Refresh All button pressed?|
You haven't actually connected the querytable to the class instance.
Private WithEvents qt As Excel.QueryTable
Public Property Set HookedTable(q As Excel.QueryTable)
Set qt = q
Private Sub qt_AfterRefresh(ByVal Success As Boolean)
MsgBox "qt_AfterRefresh called sucessfully."
If Success = True Then
|VBA Macro not pasting correctly|
Corrections (in my opinion)
If wsCombined.Range("A3") > 0 Then
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
|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
which increments by one hour.
Now you con build a pivot table. Group by the date/time value by day
and hour. Show t
|Function to count distinct values in a column range|
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
|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
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
|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
|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
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