Tag Info

New answers tagged

1

EDITED: One loop goes line by line through MPS column A, the second loop compares each MPS column A value to all of the "bom" column A values. Once a match is found, each individual cell is copied (there's a faster way to do this I'm sure, but this illustrates what's going on well) to the DData sheet, and column H gets a formula to figure your total. The ...


0

That is how it is supposed to work. From COM docs. Component Automation Exposing the Application Object Language Filter: All Language Filter: Multiple Language Filter: Visual Basic Language Filter: C# Language Filter: C++ Language Filter: J# Language Filter: JScript Visual Basic (Declaration) Visual Basic (Usage) C# C++ J# JScript ...


0

The following will do what you want and is fully configurable, in addition it supports any input or output format as long as Excel still understands it as a numerical date+time. You can set any start or end for your working hours/days. Public Function EndDayTimeM(StartTime As Double, Minutes As Long) Dim rangeH, numH, rangeD, numD, startD, durW, durD, durH, ...


1

What I was talking about in my previous answer that got downvoted, in practice: Public Function EndDayTimeM(StartTime As String, Minutes As Double) Dim start As Date, starthour As Date, endhour As Date, minutes2 As Date start = CDate(StartTime) minutes2 = DateAdd("n", Minutes, 0) starthour = 8 / 24 'working day starts at 8 endhour = 16 / 24 'working day ...


0

You'd be better off with something like this - Public Function EndDayTimeM(StartTime As String, Minutes As Double) Dim begintime As Date begintime = CDate(starttime) Dim startminutes As Double startminutes = Hour(starttime) * 60 + Minute(starttime) Dim x As Integer x = startminutes + minutes Dim endtime As Date If x < 1020 Then endtime = ...


0

Open Macros /Visual Basic, Double click on the sheet and insert this code on the right side: Private Sub Worksheet_Change(ByVal Target As Range) Dim wkb As Workbook Dim wks As Worksheet Dim wksRange As Range Set wkb = ThisWorkbook Set wks = ActiveSheet For i = 6 To 98 ' rows to be sweeped Set wksRange = wks.Range(Cells(i, 4), Cells(i, 127)) ' check from ...


0

For completeness, here's how to do it without VBA: Enter this array formula in B1 with Ctrl+Shift+Enter {=SUMSQ(1*MID(ROW(),ROW(OFFSET($A$1,,,LEN(ROW()))),1))} Enter this formula in A1: =IF(MOD(B1,3)=0,ROW(),0) Fill down for as many rows as you like. 10,000 in your case. Filter to hide values of 0 in column A OR copy / paste values and then sort A and ...


0

Do you want the Macro in Excel VBA to do this or just a pointer in a direction? This may not be the most efficient way but it is one way. Go through the range of numbers and for each number convert to a String e.g. sValue = Trim(Str(I)) then for each Digit in LEN(sValue) you get the Value and Square it e.g. For J = 1 To Len(sValue) ...


0

In Sheet2 (updated item), add a column D that checks if the product is new: =MATCH(A2,Sheet1!$A:$A,0)>0 Add a column E that gives the TRUE values in column D a unique identifier =D5&COUNTIF(D$2:D2) Now go to Sheet3 (the new items) and put an index column in, say, column E with values TRUE1, TRUE2, TRUE3 and so on, as many as you think you'll ...


1

I found a work arround to this. Instead of changing the Itens, I created a VBA script to name all the ranges linked. So instead of changing the link item in Word, I change the named range value in excel. The link item remains the same, but it points to a range that can be pragmatically modified. Here's the code I put together to put names to ranges: Sub ...


0

Actually, I'd do it a different way altogether using the PowerQuery addin from Microsoft but I don't know how well it works on Excel 2010 (it wouldn't work on Excel 2007). With that you can do append queries with grouping on you product ID so that you get a unique list. The other way to do it is to keep the sheets in different files and use the Excel query ...


0

I think You might need something like this: =VLOOKUP(Sheet1!A1:A100,Sheet2!A1:F100,6,FALSE) source: http://stackoverflow.com/questions/15396677/excel-compare-two-cell-from-different-sheet-if-true-copy-value-from-other-cell Of course You can also solve this, using VBA with buttons and all kind of fancy things.


1

I think the function call should be: Target.Hyperlinks.Add Anchor:=Target, Address:="http://stprs533/tickets/list/single_ticket/" & Target.Value, TextToDisplay:="Ticket #" & Target.Value I just changed both occurrences of c to Target, which I think will do what you're describing. The 'Object required' error is being thrown because c hasn't been ...


0

You should be able to force outlook to view the mail as plain text per relevant KB. Check there for the setting location and note this part - In the case where digital signatures are used, Outlook must display the e-mail message in the original format that is specified by the sender, or you must turn on the Read all digitally signed mail in plain ...


1

Office 2013 installs VBA 7.1. With it, an updated versions of FM20.dll - which in turn has a dependency on the VC10 redistributables - specifically msvcr100.dll. When VBA 7.1 is uninstalled - it does not remove FM20.dll - but the accompanying application most likely will remove the VC10 redist. When it does, FM20.dll is broken. It's not that it's corrupt ...


0

try activesheet.range("K:K") instead of just range. Do you have only one sheet in your files / are you sure the desired sheet is selected? Please check again whether intersection of used range and column K is really exist.


0

There is a pretty simple workaround to this. Excel's REMOVE DUPLICATE logic is to remove the duplicate that appears lower in the spreadsheet. If you would like to remove all the items from sheet 2 that appear in sheet 1, simply paste the items from sheet 2 at the top of sheet 1, run the REMOVE DUPLICATE functions for the column, and then delete all the ...


0

You can get the workbook name using activeworkbook.name - so you can easily write an if around that and use exit sub if its the wrong book


1

Starting with data like: Running this macro: Sub qwerty() Dim J As Range, L As Range, r As Range Set J = Intersect(ActiveSheet.UsedRange, Range("J:J")) Set L = Intersect(ActiveSheet.UsedRange, Range("L:L")) For Each r In J If Left(r.Text, 1) = "$" Then r.Copy r.Offset(0, 1) r.Clear End If Next ...


2

Put any Shape or AutoShape on the worksheet and assign this macro to it: Sub SaveResults() Dim N As Long If Range("N7").Value = "" Then N = 7 Else N = Cells(Rows.Count, "N").End(xlUp).Row + 1 End If Cells(N, "N").Value = Range("M3").Value End Sub


2

By using the function Tools->Macro->Record New Macro..., you can do all your operations like before (with Keyboard and Mouse) and after that (stop record) you can take a look at the generated macro-code.This can help in many cases. If you have any special problems, please post your code.


2

This does what you want. When you run VBa, there is no undo, so take a backup of your file first and play with a copy!! Please note the comments in the code, this is things you will need to update I have also assumed you're not using worksheet2 so this code uses it! Option Explicit Sub SaveAsNewRow() Dim sourceData As String sourceData = "A1" ...


0

You are missing a comma before ReplaceFormat:=False


0

Try the following VBS code: Set fso = CreateObject("Scripting.FileSystemObject") Set f = fso.GetFolder("c:\") For Each thing in f.files If LCase(Right(thing.path, 3)) = "doc" Then Set doc = GetObject(thing.path) For each wd in doc.words If wd = "cat" then Counter = Counter + 1 Next ...


1

This is a MAPI_E_INVALID_ENTRYID error. It may indicate that your PST file is corrupted. Try to run scanpst.exe utility to fix it and after that try to run your script again. Also, you may want to try running the script for items in another PST file(s). EDIT: It happens sometimes that Outlook profile is corrupted and behaves in a weird way. You can try to ...


2

Put these two subs in a standard module and the run MakeButton Sub MakeButton() ActiveSheet.Shapes.AddShape(msoShapeOval, 228#, 557.25, 126.1, 126.1).Select With Selection .Characters.Text = "1 ADDER" .ShapeRange.Fill.Visible = msoTrue .ShapeRange.Fill.ForeColor.SchemeColor = 3 .ShapeRange.Line.Visible = msoTrue ...


0

As described in the other answer you can do it with formula but it may not be the best / easiest solution, I'd use one of the below options: Pivot table: Probably the easiest and most elegant solution if you can live with its limitations: - destination range is not editable (maybe its even a positive in your case). - you can use only number and date ...


0

An entirely different approach to get selected pages printed exists in Office, LibreOffice and most likely any other variant of these. CTRL+P opens the Print dialog. Find the "(o) Pages" radio button, click it, then type the page numbers in the box beside it; as in: -2, 5-10, 17, 18, 20- or similar. By that you do not need to include special macros, and ...


0

It is possible to find the values with formulae. You'll just need the headers to show numbers, e.g. 1 with the format of 0 "st visit" etc. Unfortunately the functions will never deal with two things: data loss and overflow. Imagine the patient coming on an 8th occasion and it not showing in your table 'cause you've only filled it to 7. The function ...


0

Excel has recognized your x-axis data as dates and is trying to "help" you by including all dates in your range (2015-03-31 to 2015-04-28) on your axis-regardless of whether you have data or not. This isn't necessarily a bad thing-typically data that is being charted according to date relies upon the regular progression of data points from day to day (or ...


1

I'd do it with pivot chart: put KRA to "axis fields" and total rating to values (use max function). To facilitate updating of your chart convert your data range to data table first so you won't need to change source data range when you enter data. Also you can add very simple code to your sheets module to update chart when data changes. (just change ...


1

Give this a try: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub Application.EnableEvents = False Target.Offset(0, 3).Value = Target.Value Target.Clear Application.EnableEvents = True End Sub


1

If they're uncompressed (i.e. not DOCX format), any file-search tools, such as Windows find can count them. If they're in Zip compressed format, the free Agent Ransack can search within the files, as well as in uncompressed documents. It shows a summary with counts, as well as the name of each document with the search string and each line containing it.


0

Set all checkboxes (form controls) to invoke the same macro. In the macro you can check which particular checkbox was clicked and its various properties as follows: Dim chk As CheckBox Dim str As String Sub MyMacro() Set chk = ActiveSheet.CheckBoxes(Application.Caller) str = "CheckBox with Index=" & chk.Index & " and Name='" & chk.Name ...


0

Select cells you want to move. Now try to move them by drag n drop to different worksheet (different tab). I know, it scrolls. Here is the tricky part: just press cmd (mac) or alt (win) and it will let you drop the cells to another tab.



Top 50 recent answers are included