VBA is very EN-US-centric. VBA’s .Formula and .FormulaR1C1 expect the ROW function. To use regional language function ‘flavors’ like СТРОКА then the Range.FormulaLocal property or Range.FormulaR1C1Local property should be employed instead. The same holds true for list separator characters. Use a comma (e.g. ,) to separate the arguments in a function when using .Formula or … Read more
Yes. Set a reference to MS Scripting runtime (‘Microsoft Scripting Runtime’). As per @regjo’s comment, go to Tools->References and tick the box for ‘Microsoft Scripting Runtime’. Create a dictionary instance using the code below: Set dict = CreateObject(“Scripting.Dictionary”) or Dim dict As New Scripting.Dictionary Example of use: If Not dict.Exists(key) Then dict.Add key, value End … Read more
Dir takes wild cards so you could make a big difference adding the filter for test up front and avoiding testing each file Sub LoopThroughFiles() Dim StrFile As String StrFile = Dir(“c:\testfolder\*test*”) Do While Len(StrFile) > 0 Debug.Print StrFile StrFile = Dir Loop End Sub
The problem is that Cells is unqualified, which means that the sheet to which those cells refer is different depending on where your code is. Any time you call Range or Cells or Rows or UsedRange or anything that returns a Range object, and you don’t specify which sheet it’s on, the sheet gets assigned … Read more
Integer variables are stored as 16-bit (2-byte) numbers Office VBA Reference Long (long integer) variables are stored as signed 32-bit (4-byte) numbers Office VBA Reference So, the benefit is in reduced memory space. An Integer takes up half the memory that a Long does. Now, we are talking about 2 bytes, so it’s not going … Read more
This looks pretty straightforward. Just turn on the Macro Recorder, select the cells of interest, click Data > Text to Columns, and follow the prompts. Turn off the Macro Recorder when done. That’s it.
In the cmdUpdate button’s on click event, you need to place a procedure that will generate a sql command, then execute it. Private Sub cmdUpdate_Click() ‘Create a string variable to hold your sql command string Dim strSQL As String ‘Fill the variable with your sql command, plucking values out of your ‘form as shown using … Read more
I am providing you the VBA code for this problem, with comments to help you understand the process. Please take time to read through and understand what is happening, so next time you run into a problem like this you have an understanding of where to start. If you have a go and get stuck, … Read more
You need to avoid using Select and Activate. Set the workbooks, worksheets and ranges to variables and use them. Also when pasting just values avoid the clipboard for speed. Sub GetTW_Data() Dim tWb As Workbook Dim ebayWb As Workbook Dim tWs As Worksheet Dim ebayWs As Worksheet Dim rng As Range Set tWb = ThisWorkbook … Read more
Premise being you already hook into the Worksheet_Change()-event to show the userform (which I think you do, since it shows “whenever someone enters information into column H), you can store the values in variables as follows: Create a separate module for global variables: Public Range1 as Range Public Range2 as Range Next, in the Worksheet … Read more