Tag Info

Hot answers tagged

5

I made a solution using VBA: Public Function BikeSize(MinSize As Integer, MaxSize As Integer, datainput As String) Dim dataoutput() As Variant ReDim dataoutput(0) BikeSize = 0 datasplitted = Split(datainput, " ") arraysize = UBound(datasplitted) j = 1 For i = 0 To arraysize m = datasplitted(i) If m >= MinSize ...


3

This should work: answer = MsgBox("Do you want to export to PDF?", vbYesNo + vbQuestion, "Export to PDF") If answer = vbYes Then fileSaveName = Application.GetSaveAsFilename("", fileFilter:="PDF (*.pdf), *.pdf") If fileSaveName <> False Then ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName, ...


2

Sub Test() Dim countMe As Integer countMe = LEN(SUBSTITUTE(B1,""|"","""")) Sheets("Data").Range("C1").Value = countMe End Sub C1 should equal 8


2

The countMe line can't have two equal signs, otherwise you're essentially asking Excel if the formula in B1 is equal to "=LEN(SUBSTITUTE(B1,""|"",""""))", which it is not, so it's returnig zero. You can see this by changing the data type of countMe to Variant, and it will default to Boolean and you will get a FALSE instead of a zero. That's like saying: ...


2

2 ways to count occurrences of "|" in string "Test|Test": . 1 - Formula Sub Test1() Dim countMe As Long With Sheets("Data") .Range("C1").Formula = "=LEN(B1)-LEN(SUBSTITUTE(B1,""|"",""""))" 'your formula countMe = .Range("C1").Value2 'count will be "1" .Range("D1").Value = countMe End With End Sub . 2 - VBA ...


2

Here's the trick how to keep your color selection permanent. A bit more hex editing. If you made replacement suggested by Bond you should have the following at your hand: RGBA values for basic (classic) 16 colors indexed from 0 to 15, and RGBA values for your custom colors indexed the same way. Now search VBEx.DLL for bytes sequence built from "classic" RGBA ...


2

You will need to add them to the .acl file as explained in this KB. To use your AutoCorrect entries on another computer (or to share them with another user profile on your computer), locate the .acl files in the %AppData%\Microsoft\Office folder. (You can type the path in Windows Explorer exactly as shown here and the %AppData% environment ...


2

If you're open to VBA, there's the Application.AutoCorrect object that has .AddReplacement method KB. You could do something like this - Sub test() Dim rng As Range Set rng = Range("A1:A100") With Application.AutoCorrect For Each c In rng .AddReplacement c, c.Offset(, 1) Next End With End Sub That's assuming you list is in A1:B100 where ...


1

there was no typo. "rows(51)" is valid. but you don't need the "entirerow" modifier rows(n).hidden=true ; where n is an valid row number to hide multiple rows range(rows(n1),rows(n2)).hidden=true ; will hide rows n1 though n2


1

I know this is old, but as a reference: Option Explicit Private Const Q As String = "'" Private Const ROOT As String = "E:\Test\" Private Const FLDR As String = "SubFolder" Private Const DASHBRD As String = "Db.xlsx" Public Sub updateAllFiles() Dim ws As Worksheet, cn As ADODB.Connection, rs As ADODB.Recordset, sql As String ...


1

Have you tried setting the calculations to manual? (In Excel 2013) Formulas - Calculation Options - Manual It looks your intent is to remove all occurrences of the values in column "H" in the values in column "E". Have you considered exporting the content and using a tool other than excel to perform the changes you desire?


1

Your code is updating the values in column E by removing any values it finds in column H. However, it is doing it very inefficiently by looking at only one cell each time. You can do much better by dealing with the entire range in column E at once. Also, even when you're looking at a single cell, it's easier to use a Range object to access it rather than ...


1

In programming you can specify which file type you are searching for using the attributes of that file. Here is a good example: https://msdn.microsoft.com/en-us/library/7b748c15(v=vs.90).aspx You will simply look for files that match your desired file type (not hidden). Do keep in mind that your method of deleting will more than likely be recursive as ...


1

You need to refer to the sheet differently - it's just one of those things. Option Explicit Sub test() Dim pLWB As Workbook Dim pLWS As Worksheet Set pLWB = ActiveWorkbook Set pLWS = pLWB.ActiveSheet Workbooks.Add pLWS.Range("A1").Copy End Sub Also this works - Option Explicit Sub test() Dim pLWB As Workbook Dim pLWS As String Set pLWB = ...


1

As a VBA reference, all of the following statements will perform the task: Sub removeDuplicates() With ActiveSheet.UsedRange .removeDuplicates 'duplicated rows based on all cols .removeDuplicates Columns:=Array(1, 2, 3) 'duplicates based on cols 1, 2, and 3 .removeDuplicates Columns:=Array(3) ...


1

This should do it: Sub dural() Dim N As Long, i As Long, K As Long, v As Long Dim vOld As Long N = Cells(Rows.Count, "A").End(xlUp).Row + 1 K = 1 For i = 1 To N v = CLng(Cells(i, 1).Value) If i = 1 Then Cells(1, "B").Value = v vOld = v Else If v = vOld + 1 Then Else Cells(K, "C").Value = vOld K ...


1

This code should work: Public Sub searchfullname() fullname = InputBox("Input first and last name separated by _") namesarray = Split(fullname, "_") i = 2 dataintable = True result = "No" m = ActiveSheet.Cells(i, 1) If m = "" Then dataintable = False While dataintable = True result = "No" If m = namesarray(0) ...


1

The reference to Cells isn't on the same sheet as the call to Range. This is mentioned in the docs for Range.Item. It will will work if Database is the active sheet but not otherwise. Try replacing the error line with With Sheets("Database") .Range(.Cells(i, 11), .Cells(i, 24)).Copy End With Note the periods before Cells.


1

This is an interesting question, and I can see your question is actually 2 parts. Why does the macro choose Split instead of Pane and How to work around it. I don't know the answer to 1, so I'll focus on 2 only. Since you want to unfreeze you need to use freeze! Sub DoThis() Columns("E:E").Select ActiveWindow.FreezePanes = True End Sub


1

This will create a new sheet called "For MySQL" and place the result there: Option Explicit Public Sub rowToCol() Const FC As Long = 1 'first col (ID) Const FR As Long = 2 'first row Const NEXT_COL As Long = FC + 1 Const DB_WS_NAME As String = "For MySQL" Dim ws As Worksheet, db As Worksheet, lr As Long, lc As Long, maxRow As Long ...


1

Here is a small macro to directly export your data to text file without the need for an additional sheet Change "D:\sql_import.txt" to your desired output path Change [B2:E4] to your desired Excel input range VBA Code Sub ExportForSql() Open "D:\sql_import.txt" For Output As #1 For Each cell In [B2:E4] If Not cell.Value = vbNullString ...


1

The first Microsoft KB that shows up for the Personal Macro Workbook in OSX erroneously lists the location of the workbook as: Hard disk drive:Applications:Microsoft Office version:Office:Startup:Excel:Personal Macro Workbook In OSX Yosemite (10.10), the location of the Personal Macro Workbook is different. Starting at /home/ or /Users/$username: ...



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