Tag Info

Hot answers tagged

3

Sure, there's an easy way to do this in the VBE, but it requires you enable a button (or two) on your toolbar - Right click your toolbar and select customize In the commands menu, select the edit category and about halfway down you will see two options - Comment Block and Uncomment Block Select these and drag them up to the toolbar. Now when you ...


3

The property is Buttons not vbMsgBoxStyle. Your code should read like this: VBA.Interaction.MsgBox _ Title:="Invalid Scope!", _ Prompt:="You entered Invalid Scope !!!", _ Buttons:=vbCritical


2

I will explain how to put this macro into Excel as simply as I can. To add a macro, press Alt + F8. This will open the Macro window. Then type a name for a new macro and click Create. VBE will open and you can paste the below code in between Sub and End Sub. Dim WordApp As Word.Application Dim myDoc As Word.Document Application.ScreenUpdating = ...


2

Original Source from Excellll on SO is here. His code. The easiest way would be to take the lists, independently, and store them in an array. Then use the arrays to output all possible combinations. This code will output in 3 different columns, but you can concatenate if you need them in a single cell Sub combinations() Dim c1() As Variant Dim c2() As ...


1

So something like this will take the range A1:10 and copy it to C1:D10 Sub test() Range("A1:B10").Copy Range("C1").PasteSpecial xlPasteValues End Sub What we need is a way to calculate how large the range will be and use that while we iterate. Something like this should work - Sub test() Dim start As Integer Dim finish As Integer Dim change As Integer ...


1

Just add the following to the end of your code: dim nextrow as integer if Range("'Sheet2'!A2").value = "" then nextrow = 2 else nextrow = Range("'Sheet2'!A1").End(xlDown).Row + 1 end if Range("'Sheet2'!A" & nextrow ).value = sName Range("'Sheet2'!B" & nextrow ).value = CurrTime Change Sheet2 to whatever your sheet name is. Also I assume ...


1

To my mind, there is a little ambiguity in your description but this is what I've come up with to exactly conform to the requirements specified. Dim r As Long For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 If IsNumeric(Cells(r, 1).Value) And CBool(Len(Cells(r, 1).Value)) Then _ Rows(r).EntireRow.Insert shift:=xlDown Next r


1

Updated formula. Finds the cell with the first type and second type and looks for the word widget between it. If it exists return the value beside type if not return blank. =IFERROR(IF(MATCH("widget",INDIRECT("A" & SMALL(IF(A:A="type",ROW(A:A),1000000),E1) & ":A" & ...


1

Firstly I think you could simplify a lot of the code in your macro by using Mail merge from the Access database instead and just instigating that via macro. The field code for your IF field as it appears here should work. Make sure that you have surrounded the code with special field brackets by clicking CTRL + F9 and not just regular brackets.


1

Excel is automatically converting that text you inputting with the macro into a date (and then applying a date format. To force excel to view it as text, prepend a single quote to the date string: ActiveCell.Value = "'" + Format(Now(), "dd-mmm-yy") Alternatively, set the format of the cell to text type first: ActiveCell.NumberFormat = "@" ...


1

Untested code, record cell A3 (the list selector) into B3: Sub MemorizeCellA3 Range("B3").Value= Range("A3").Value ' might need to be .Text instead Range("A3").ClearContents 'will clear out A3 end sub Assign it to a button on the sheet. Source of code


1

If you want to do this automatically, put this macro in the worksheet module of the sheet that you want to do this on Private Sub worksheet_change(ByVal target As Range) If Not Intersect(target, Range("A:A")) Is Nothing Then If target.Offset(, 1) = "" Then target.Offset(, 1) = target End If End If End Sub The range("A:A") is the range ...


1

To accomplish the title of your post you are looking for Now with some date manipulation. Sub STUFF() Dim msg As Outlook.MailItem Set msg = Application.CreateItem(olMailItem) msg.To = "mcameron@firstair.ca" msg.CC = "jgencher@firstair.ca" msg.Subject = "STUFF STUFF STUFF" & Now msg.Body = "PERSON, STUFF STUFF STUFF" & Now ...


1

Yes, you'd need something like this in the worksheet module for the workbook - Private Sub worksheet_change(ByVal target As Range) If Not Intersect(target, Range("Z3")) Is Nothing Then If Range("Z3") = "CSI" Then Range("B3") = Range("A3") Range("A3").ClearContents End If End If End Sub You could change the range to reference the ...



Only top voted, non community-wiki answers of a minimum length are eligible