IvenBach
  • Member for 4 years, 11 months
  • Last seen this week
  • Irvine, CA, United States
Data entry of Information
Accepted answer
14 votes

You include Option Explicit :+1 for that alone. Range without any explicit worksheet implicitly is working off of whatever worksheet happens-to-be-active-at-the-time-that-code-is-executed. This will ...

View answer
Saving worksheets as pdf and sending per mail
Accepted answer
8 votes

All your arguments can/should be passed ByVal. As they are presently written they are implicitly ByRef, the default when neither is specified. You want them written ByVal since you're accessing them ...

View answer
Survey Populate with List, Option Label and Formula, VBA
6 votes

Turn on Option Explicit. From the VBIDE menu at the top Tools>Options to display the Options dialog>Editor tab>Code Settings group>Require Variable Declaration. Tick that check box. From that point ...

View answer
Utilize loop or other efficient way to find string and copy offset columns to certain cells
Accepted answer
6 votes

Option Explicit is missing from your code. From the menu at the top under Tools>Options>Editor tab>Code Settings group>Require Variable Declaration <-- toggle that check box on. It will thereafter ...

View answer
Extracting filenames from a URL by splitting a string
Accepted answer
5 votes

Abstract the implementation into a function. This makes it easy to catch mistakes if you want to use this capability in more than one location. GetUpperBoundElemenentFromDelimitedString takes the ...

View answer
VBA to retrieve/display information of cells in excel
4 votes

Having Option Explicit is a great start :+1: for that alone! Your Const MDP doesn't need to be declared outside your Sub. In fact it should be a local variable to that Sub as it's only used there. ...

View answer
Checks data already stored in specific worksheets then populates data as needed
Accepted answer
4 votes

The variable lastRow isn't defined anywhere. From the menu Tools>Options>Editor tab>Code Settings group>Require Variable Declaration. Make sure that box is checked. It mandates all variable be ...

View answer
Loop with multiple conditions that need to be true, across two different sheets
4 votes

Qualify your variables. I read your post and after copying/pasting the code I created a standard module named GlobalVariables so that I had O.Offset(GlobalVariables.OFFSET_ROW, GlobalVariables....

View answer
Standardize and reduce repeating same procedures in my coding Excel VBA
Accepted answer
4 votes

If you're going to do any developing in VBA then you need to check out Rubberduck . Open disclosure, I'm a contributor. I've used it for long enough now that the IDE doesn't feel complete without it. ...

View answer
Adds text to specific cells then bolds one column and copies it to target sheet removing bolded column. Makes last column red
Accepted answer
3 votes

Turn on Option Explicit. From the menu at the top Tools>Option>Editor tab>Code Settings group>Require Variable Declaration check box. Make sure that is checked. This mandates that you use Dim foo as ...

View answer
Populate dates in a label based on dates in a hidden sheet using IIF
Accepted answer
3 votes

Declare your variables right before you use them. For your cap variables, assuming these stand for caption (more on that with descriptive variables), you have a Declare, Populate, Use in a 1-2-3 ...

View answer
Reduce time taken for VBA loop when copying multiple sets of input values, performing Excel formula calculations and recording results
3 votes

Use descriptive variable names. r as a variable name tells you nothing and makes your code harder to read. rowOffset tells you what it's used for. This goes a long way to make your code readable. ...

View answer
Simplify & Reduce steps in the IsDistinct function
3 votes

Nested ifs are something I've come to knee-jerk refactor into similar below. Writing functions that state what they're doing reduces the burden to understand. When future-Iven comes along and ...

View answer
Calculations for trucks
3 votes

You are missing Option Explicit at the top of your code module. Add it. From the menu Tools>Options to display the Options dialog. In the Options dialog>Editor tab>Code Settings group>Require ...

View answer
Speed Up Excel Macro - Autofilter Copying
Accepted answer
3 votes

Turn on Option Explicit from the menu via Tools>Options>Editor tab>Require Variable Declaration. This mandates you Dim lastRow as long before using it anywhere. Doing this will save you needless ...

View answer
Format 2 Excel sheets then save both sheets as a new workbook
3 votes

Most times you have Range.Select followed by Selection.Something you can remove the Select and Selection to leave it Range.Something. This helps clarify what the code is doing. The same may be true of ...

View answer
Slow performance in looping and filling missing data
3 votes

It looks like you just need VLookup. In column B1 of your active sheet you'll want =VLookup(A1,Feuil1!$A$1:$B$4,2,0). Make sure to edit Feuil1!$A$1:$B$4 to be the entire range you want. If your range ...

View answer
Worksheet change and worksheet selection change slowing down my file
2 votes

You're already declaring your variables :+1:. But I'll still mention that Option Explicit should be turned on. From the menu at the top Tools>Options>Editor tab>Code Settings group>Require Variable ...

View answer
Retrieve status for a list of FedEx tracking numbers
Accepted answer
2 votes

Stringly typed values. DoSimpleChecks can/should be typed as Boolean and renamed. Presently it's checking against a string value "Not Ready". It's also activating cells which means it's doing more ...

View answer
Bookkeeping Coding - Check and Create Tabs, Copy filtered Data in Loop - EDIT new/better code posted as new thread
Accepted answer
2 votes

You're missing Option Explicit from the top of your code. From the menu at the top choose Tools>Options... to display the Options dialog>Code Settings group>Require Variable Declaration. Make sure ...

View answer
Array that stores values from two ranges and compares if one cell is blank
Accepted answer
2 votes

The fileArray variable can be replaced with 2 variables. When looking at fileArray in the locals window, from the IDE menu at the top View>Locals Window, you can see that it's it a Variant/Variant(0 ...

View answer
Find select values on one worksheet and copy to a second
2 votes

@Raystafarian's already commented on .Cells and String subtraction so I wont repeat it. Making your code as easy to read as possible will help. Your target variable was redundant as it was only ever ...

View answer
Using buttons to trigger a Sub Procedure in Excel, this will split/copy data into a second sheet in the same workbook
2 votes

It looks like you've already done this - Tools>Options>Require Variable Declaration - which makes sure you Dim your variables. If not do so, now, future-you will thank you. You are using s1 and ...

View answer
Update PowerPoint graphs from Excel file
Accepted answer
2 votes

Option Explicit +1 For having that. Declare your variables right before you use them. Dim SecondsElapsed As Double is declared at the very beginning, and not used until the very end. This leads to ...

View answer
Applying same formatting to multiple borders in Excel VBA?
2 votes

You can also use. I added the selection range so you get intellisense. Dim selectionRange As Range Set selectionRange = Selection Dim theLineStyle As XlLineStyle theLineStyle = xlContinuous With ...

View answer
Optimizing Macro that Generates File Based off different Source Tabs
Accepted answer
1 votes

Your variable for DEM_ERow looks to be more complicated than it needs. DEM_ERow = wsDEM.Range("C4").End(xlDown).Row should produce the same row count. A lot of your Subs were passing in both the ...

View answer
excel vba code for a report generating then save & open in pdf
1 votes

You are looping without the need to do so. If I've understood your example you can replace your loo For i = 11 to final ... Next with what follows below. Since you're looking at each row in column 2 ...

View answer
Copying and separating data by Excel Field
1 votes

Declaring your variables right before using them will help you remember what they're for. At the top you have a large block of declarations before you get to your code. Moving them to where they get ...

View answer
VBA code that compares 2 sheets from different systems
Accepted answer
1 votes

You turn off ScreenUpdating and Calculation and never turn them back on. Make sure you do that. Application.ScreenUpdating = False Application.Calculation = xlCalculationManual You have your ...

View answer
Parsing a website, saving tables in Excel
1 votes

Having consistent indentation will help readability. Declaring your variables closer to where they are used also helps clear things up, firstAddress isn't used anywhere. Using Alt+D+L (Debug>Compile) ...

View answer