Tag Info

Hot answers tagged

2

So this looks up the value of the last non-blank =LOOKUP(9.99E+307,B:B) So then, this would lookup the row of the last non-blank =MATCH(9E+99+307,B:B,1) If you want to know what's next to it, you can use indirect - =INDIRECT("A"&MATCH(9E+99+307,B:B,1)) Or you could use an index/match (probably a better choice) =INDEX(A:B,MATCH(9E+307,B:B,1),1) ...


2

Here's the code snippet to do that, assuming you want to modify the currently selected cell: Sub Macro1() ' ' Macro1 Macro ' ' Keyboard Shortcut: Ctrl+q ' On Error Resume Next 'In case "Selection" is something that doesn't have a NumberFormat property If Selection.NumberFormat = "0" Then Selection.NumberFormat = "0.00" Else: Selection.NumberFormat = "0" ...


2

This VBA code will do it: Private Sub Worksheet_Change(ByVal Target As Range) Dim wk As Workbook Set wk = ThisWorkbook Dim ws As Worksheet Set ws = ActiveSheet WatchedColumn = 2 BlockedRow = 1 TimestampColumn = 4 Crow = Target.Row CColumn = Target.Column If CColumn = WatchedColumn And Crow > BlockedRow Then Cells(Crow, TimestampColumn) = Now() End If ...


2

You can do this with VBA, something like this - Sub test() Dim ws1 As Worksheet Dim ws2 As Worksheet 'rename sheet1 to point to the data you want to change and 'ws2 to the sheets you're looking up from Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") Dim rngsearch As Range Dim rngfnd As Range 'These point to the SKU columns, change as needed ...


2

Replace the line: i=20 with: With ActiveSheet i = .Cells(.Rows.Count, "A").End(xlUp).Row End With It counts the number of rows with data on column A. By the way, I tested your macro and it doesn't add a row.


2

Give this a try: Sub hjksdf() Dim lastRow As Long With ActiveSheet lastRow = .Range("B" & .Rows.Count).End(xlUp).Row End With For i = lastRow To 1 Step -1 If Cells(i, "B").Text <> "#N/A" Then Exit For End If Next i lastRow = i MsgBox i End Sub EDIT#1: Here are ways to find the last ...


2

if you are using Excel 2010 and above, then Excel provides you with in internal add-in which allows you to save the file as PDF format. Use the following code in your macro to save your file as PDF ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\Users\46506090\Desktop\Book1.pdf", Quality:=xlQualityStandard, _ ...


2

The full path to the root User folder of the currently logged-in user can be retrieved with: Environ("USERPROFILE") To save the file to the user's desktop, like you've done, you would use: Environ("USERPROFILE") & "\Desktop\" & ... For your specific code as provided ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ ...


1

Step-1: I donot know if you have the developer tab or not in your Excel. If you do not see a "Developer" tab next to your View tab, follow the instructions here. to get it done. Step-2: As shown below locate the command button and click on it. This allows you to draw the command button in the location you like. Step-3: Click on the button next to ...


1

Either just use the worksheet-formula in your macro as well, i.e. Function GetLastValueInColumn(r As Range) As Range GetLastValueInColumn = Application.WorksheetFunction.LOOKUP(2,1/(r<>""),r) End Function Or use some VBA specific methods, a couple are detailed in this answer on StackOverflow.


1

You need to make another macro or another code like this (in VBA) : Public Sub runMacro() For i = 1 To 30 ' Body of your macro and set the row number by i ' Cells(1, 1) = i 'Enter 1-30 numbers to cell A1 ' Call pdfMacro '<pdfMacro> is your current macro Next i End Sub


1

I may not understand your example clearly, but it looks like you could just use one of these cell formulas: =IF(ISBLANK(B5),"",TODAY()) =IF(ISBLANK(B5),"",NOW()) =IF(ISBLANK(B5),"",Updating_Date(B5)) If you want to do the check in VBA, here's an option: Function Updating_Date(dependent_cell As Range) If LenB(dependent_cell.Value) > 0 Then ...


1

Create a boolean variable, set it to false before the loop and change it to true only when the comparison of dates is true. Then, before sending the email check the state of the variable. You can do these changes: 1 - Before the loop, above the line For Each rngCell In .Range(rngStart, rngEnd) put the line ValidDate = False. 2 - After If rngCell.Value - ...


1

There are 2 ways to solve the issue: Using an IF Just add a second if statement in your for loop and place the code inside that if. (see the 2 separate lines in the code below for what to add) Your for loop code would look like this: For Each HeadCell In Range("C1:C" & lastRow) If Len(HeadCell) < 6 And Len(HeadCell) > 1 Then if HeadCell ...


1

Put it as excluyent option through elseif of txtMileage.Text = "". There won't be a chance that it does the check if >300 when is empty. Private Sub cmdCalculate_Click(sender As Object, e As EventArgs) Handles cmdCalculate.Click If btnNo.Checked = False And btnYes.Checked = False Then MsgBox("Please select yes or no") Exit Sub ...


1

How about just checking if .Text contains a numeric value and, if it doesn't, setting it to be 0? If Not IsNumeric(txtMileage.Text) Then txtMileage.Text = 0 You may or may not want to store it as a variable instead to avoid changing the control. Dim txt as Variant txt = txtMileage.Text If Not IsNumeric(txt) Then txt = 0


1

I have found this way: Open the file(s) that contain your locked VBA Projects Create a new xlsm file and store this code in Module1 Option Explicit Private Const PAGE_EXECUTE_READWRITE = &H40 Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _ (Destination As Long, Source As Long, ByVal Length As Long) Private Declare ...


1

Begin by removing all the unnecessary loops. Replace: For j = 41 To 60 ThisWorkbook.Worksheets("Breakdown").Cells(j, "A") = "" ThisWorkbook.Worksheets("Breakdown").Cells(j, "D") = "" ThisWorkbook.Worksheets("Breakdown").Cells(j, "E") = "" ThisWorkbook.Worksheets("Breakdown").Cells(j, "F") = "" ...


1

I changed the part you needed via introducing a boolean variable bFlag. Private Sub FloorWallTileCombo_Click() Dim TileSearch As String Dim TotalPrice As Double, TotalSF As Double, TotalSurCap As Double, TotalCorCap As Double Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Breakdown") Dim bFlag As Boolean ws.Range("A41:A60").Value2 = vbNullString ...


1

I decided to use a VLOOKUP solution instead. Create another sheet with 1 column of SKUs and the second column of "Not Visible Individually" from top to bottom. Then ran the function from top to bottom in column U on list1 ("visiblity" column).


1

With data like: Running this macro: Sub RowYourBoat() Dim r1 As Range, r2 As Range, r3 As Range Set r1 = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) Set r3 = Nothing For Each r2 In r1 If r2.Interior.ColorIndex = 1 Then If r3 Is Nothing Then Set r3 = r2 Else Set ...


1

I know there's already an answer, but this VBA is a bit cleaner - Private Sub worksheet_change(ByVal target As Range) If Not Intersect(target, Range("B:B")) Is Nothing Then target.Offset(0, 2) = Now() End If End Sub To adjust for your other requirement not in the question just add an if - Private Sub worksheet_change(ByVal target As Range) If Not ...


1

After installation of MySQL for Excel, startup Excel, go to the "Data" ribbon and on the right you should see a "MySQL for Excel" button. That should be the best (and preferred) way to connect to a MySQL database, using MySQL for Excel. If you need to aggregate data from different tables, the best place to do that is in the database. Create a view there ...


1

If your Outlook account is POP3 or IMAP, you can not run macros or rules while your client is closed because there is no way to trigger your event other than incoming messages. If you are running Outlook on Exchange, you would need to create a rule on the server for your account to save attachments before delivered to your Inbox. The rule will need the UNC ...


1

Try this: Sub CopyUnique() Dim s1 As Worksheet, s2 As Worksheet Set s1 = Sheets("Main") Set s2 = Sheets("Count") s1.Range("B:B").Copy s2.Range("A1") s2.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo End Sub


1

You cannot quickly select all floating pictures in an Excel sheet. But you can do a workaround. Create a new sheet. Then use CTRL-A to select all. Copy all cells Go to the new sheet Paste, and verify that this is acceptable and contains everything you need Delete the old sheet.


1

Quickly - Hit Alt+F11 to bring up the VBE hit Ctrl+G to bring up the immediate window type activesheet.shapes.selectall hit enter Go back to sheet and all will be selected, so you can delete OR If you didn't want to go back to the sheet, just type selection.delete and hit enter


1

Without knowing how you'd like to do this, a simple way would be to use an indirect So on your summary, if you start in row 1 - =INDIRECT("Sheet"&ROW()&"!$A$1") That will show A1 on Sheet1 Row 2 will show A1 on Sheet2 etc. Another way to approach it would be with a macro Sub gather() Dim wssrc As Worksheet 'if this stays as activesheet, ...


1

Some trying and failing brougt me to this sub, of which you can just paste the relevant bits into your own. As you can see I have used Chr(34) instead of double quotes to construct the string, because I think that increases readability, that is a personal preference though. Further, you'll notice that I stored the string in a variable, which is not strictly ...



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