Vba Setting Range in For Loop

I am trying to set the range in For loop. My code works fine when I do this: For Each i in Range("A1":"A5") 'Some process code Next i But I do not get the same results when I do this: For Each i in Range("A1").End(xlDown) 'Some Process Next i Arent the two codes equivalent? What changes should I make to the second one that it perfoms the same way as the first one but doesn't make me hardcode the Range in the code?

Vba Automatically transfer data on certain conditions

It's my first time using this website and I'd greatly appreciate if someone could help me write a code for Macro in Excel to do the following. Situation: I have 8 data sheets called Data A, Data B, ..., Data H. I have 1 summary sheet called Summary. On each of 8 data sheets, there are n number of IDs from cell C8 and on horizontally (i.e. C8, D8, E8, ...) . Each ID has associated data under the cells vertically. (i.e. ID in cell C8 has relevant data on C9, C10, C13, C14, C15). To do: Upon

Word VBA: Get Range between Consecutive Headings

I looked up some examples, but I cannot quite understand how the Range object works. I am trying to loop through each of my headings (of level 4) and have a nested loop that looks through all the tables in between the headings. I cannot figure out how to set that specific range, so any help will be greatly appreciated. Dim myHeadings As Variant myHeadings = ActiveDocument.GetCrossReferenceItems(wdRefTypeHeading) For iCount = LBound(myHeadings) To UBound(myHeadings) level =

Vba Hide and Unhide Blank Rows With the Same Button

I have written the below code to hide blank rows within the used range of a worksheet. The code works perfectly fine. I have assigned this macro to a button on the worksheet. Clicking the button hides the blank rows within the used range. Question: How do I modify the code so that clicking this same button does the reverse? If the blank rows are unhidden, then clicking the button hides them; and if they are hidden, then clicking the same button unhides them? I want to execute both procedures wi

vba condtional format loop failing

I have this code that almost works: Sub condFormat() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Columns("B:B").FormatConditions.Delete ws.Columns("B:B").FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(B1)>100" ws.Columns("B:B").FormatConditions(1).Interior.ColorIndex = 3 Next End Sub But it won't update the actual cell properly. I end up with stuff like: =LEN(B65517)>100 in column b cells. How can I revise this to reference the correct

VBA Excel — Select Range from String

I need to select all rows smaller than 16 points in order to manually delete them. Programatically deleting them rescales images on the spreadsheet and breaks it (ChemBio generated chemical structures). My code works up until it makes the selection: Sub FindAndRemoveSmallRows() Dim a As Range, b As Range, c As String Set a = Selection For Each b In a.Rows If b.Height < 16 Then c = c & b.Row & ":" & b.Row & "," End If Next If Right$(c, 1) = "," Then c = Left$(

VBA Writing file in binary mode (overwriting given byte makes unexpected change in the next one, setting it's value to 0)

I want to modify part of .bmp file using VBA, everything works great except that, when I overwrite choosen byte, the next byte is being set to zero. My macro is : Sub WriteBinaryFile() Dim i As Integer Dim nFileNum As Integer Dim sFilename As String sFilename = "C:\Users\Piotr\Desktop\test1.bmp" ' Get an available file number from the system nFileNum = FreeFile ' Open the file in binary mode. Locks are optional Open sFilename For Binary Lock Read Write As #nFileNum ' Put the data in

Vba Test if two range objects refer to the same range

I want to find a smarter way to test if two range objects, in fact, refer to the same range: Set A = Range("B1:B3,A2:C2") Set B = Range("B1,A2:C2,B3") Set C = Range("A2,B1:B3,C2") Set D = Range("B1,A2,B2,C2,B3") The function I'm trying to write must return True when comparing any pair of ranges described above, and False when comparing any of those ranges to a range containing cells that are not part of the first range or not containing some cells from the first range. What algorithm other t

VBA - Split string into individual cells

I have a string compressed into one cell. I need to separate each part of the string into their own cell, while copying the data from the same row. Here is my example data: A | B Row1 ABC ABD ABE ABF | CODE1 Row2 BCA DBA EBA FBA | CODE2 Row3 TEA BEF | CODE3 The result would be: A B ABC CODE1 ABD CODE1 ABE CODE1 ABF CODE1 BCA CODE2 DBA CODE2 EBA CODE2 FBA CODE2 TEA CODE3 BEF CODE3 I have about 2000 rows and woul

Vba AUTOFILL IN EXCEL MACRO

I am trying to use macro recorder in Excel to record a macro to fill down a column of cells, however because the fill down each time is a different number of cells it either fills down to short or too long and this seems to be because the macro identifies the cell range and its fixed. What I need is to auto populate or auto fill down from: ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],"" "",RC[2])" Range("C1").Select Selection.AutoFill Destination:=ActiveCell.Range("A1:A261") ActiveCell.Range("

VBA Excel work with strings

I have following string values in cells like "EnglishText / OtherLanguageText" or "UniversalText" I need to get 2 substrings from that like "EnglishText" and "OtherLanguageText" or "UniversalText" (text which not contain "/") Im selecting data with command wbImportFrom.Sheets("Sheet 4").Cells(4, 6 + x).Value How can i do that ? :-) Thank you

Vba Debug.Assert behavior in MS Access runtime

In many compiled languages, calls to Debug.Assert, or their equivalent, are left out of the compiled production code for performance reasons. However, calls to Debug.Assert still appear to execute in the /runtime versions of MS Access applications. To test this, I added the following to my startup form: Private Sub Form_Load() Debug.Assert UserOK() End Sub Function UserOK() As Boolean UserOK = MsgBox("Is everything OK?", vbYesNo, "Test Debug.Assert") = vbYes End Function When

Word VBA hide macro from macro list

I know you can use Option Private Module to hide macros from the macro list, but apparently this is not true for public functions in class modules that do not have an input parameter. Any ideas how I could hide them? This is the public procedure in my class Its appearing in the macro list:

Vba Excel count occurrences of a value

I have been given data which is associated by postal code. I need to count the number of occurrences of certain values in certain columns. Sample data is below. Survey# PostCode 1 2136 1 2136 2 136 2 2136 1 2137 2 2137 I would like to take this data and produce this type of excel workbook: Postal Code Survey #1 Survey #2 Survey #3 Survey #4 2136 2 2 0 0 2137 1 1 0 0 I have t

Vba Combine Rows with duplicate values, merge cells if different

I have similar question to [combine Rows with Duplicate Values][1] Excel VBA - Combine rows with duplicate values in one cell and merge values in other cell I have data in this format (rows are sorted) Pub ID CH Ref no 15 1 t2 no 15 1 t88 yes 15 2 t3 yes 15 2 t3 yes 15 2 t6 compare adjacent rows (say row 4 and 5) , if col 2 and 3 match then if col 4 different merge col4, delete row. if col 2,3,4 match

Vba How to make sure that hotkey only launches UserForm for active workbook?

In a template's workbook_open event, I have a hotkey ^m registered to launch a UserForm DataValidation: Private Sub workbook_open() Application.OnKey "^m", "launchDataValidation" End Sub Sub launchDataValidation() DataValidation.Show End Sub It's possible that there will be multiple workbooks open at the same time that are built from this template. I want to make sure that when the user presses the hotkey, the UserForm that is launched is from the active workbook, so that any operations

VBA SaveAs method not saving file, no errors?

Before anyone quickflags this: no, I did not forget to actually save the file after GetSaveAsFilename. Basically, I've got a big VBA module that starts with an Excel file, processes a bunch of data, and generates a summary in Excel. I want it to be impossible to overwrite the file, and I need it to work in all cases (network drives, opening from email, etc). This is why I thought it would be best just to open a SaveAs box--leave the onus of the path on the user. However, when I trigger this met

Vba Lifetime of static variable used as item counter

I have declared a static variable to use as an item counter. The spreadsheet is going to be updated by different people running the code and saving the workbook afterwards. However, when I test my code and make any changes to it, the static variable resets to 1: Static strn, mechn, ein As Integer Dim i, lrow As Long Dim num as String Set wb = ThisWorkbook Set ws = wb.Sheets("test") lrow = ws.Range("A" & Rows.Count).End(xlUp).Row i = 1 If reqb = "blah" Then Do Until ws.Range("B" &

How do I get the updated value of a textbox in VBA Access in a custom Sub?

This is for VBA Access 2003 I've got a textbox I want to use as a filter for a list box rowsource command. I also have a checkbox which adds another filter for the same rowsource command. I've only programmed in C# and I'm trying to write a single Sub which will simply set the RowSource regardless of if my textbox filter is changed or if my checkbox filter is changed. However, my textbox is giving me problems. If my checkbox filter changes and I run my method the textbox.Text throws an erro

Vba Create or clear Hashtable instance for each new request of Report in SSRS

I have a report done via VS2008 SSRS that is uploaded to my Reporting Services Server, that report is called when the event GenerateReport is called (on button click) Now, I have the following code: Public Shared Dim gruposVolTotal As New System.Collections.HashTable() Public Shared Dim gruposSeleccion As New System.Collections.HashTable() Public Shared Dim gruposModVenta As New System.Collections.HashTable() Dim grupoActual as String = "" Public Shared Dim acumuladoSeleccion as Double = 0

When does VBA change variable type without being asked to?

I am getting a runtime error I don't understand in Excel 2011 for Mac under OS X 10.7.5. Here is a summary of the code: Dim h, n, k as Integer Dim report as Workbook Dim r1 as Worksheet Dim t, newline as String Dim line() as String newline = vbCr ' ' (code to get user input from a text box, to select a worksheet by number) ' ReDim line(report.Sheets.Count + 10) MsgBox "Array line has " & UBound(line) & " elements." '----> 21 elements line = split(t, newline) h = UBound(line) MsgB

Vba Import CSV files to Excel

I'm trying to import Csv file to excel using VBA code, I want it to work with all Csv files but it didn't. Here is my code: Sub Input_CSV() Dim Wb As String Dim Arr Set Wb = GetObject(Application.GetOpenFilename("csv file,*.csv", , "please choose a csv file", , False)) Dim blnImportData As Boolean blnImportData = ImportCSV(Wb, "Sheet1", "A1") If blnImportData Then MsgBox "Import CSV process complete", vbInformation, ThisWorkbook.Name _ Else MsgBox "Import CSV process fail

Error 9 : subscript out of range Excel VBA

Hello I am trying to define the Range via some value in sheet, but i am unable to do this. Also i am not sure what causing this issue. My Code Dim row As Range For Each row In [table2].Rows For Each c In Worksheets(row.Columns(row.ListObject.ListColumns("Sheet").Index).Value) .Range((row.Columns(row.ListObject.ListColumns("RangeBegin").Index).Value) + ":" + (row.Columns(row.ListObject.ListColumns("RangeEnd").Index).Value)) If c.Value = "O" Then Sheets("master").Cells(2, 3).Copy

Fill down a row on VBA

I am trying to find out how to fill down (or copy?) a row (lastUsedRow) up to the last row. However I find myself struggling with designating ranges (especially because I am working on different datasets that have different sizes). Before I need to spot the lastUsedRow (lastUsedRow = .Range("A" & .Rows.Count).End(xlUp).Row) - which is row 31 here. It designates the latest row where there was data in column A. Then I want to tell VBA to fill down until the last row (lastRow) - row 39 - whi

Excel VBA UserForm, progress bar

I've created a userform that should act like progress toolbar and show me my actual progress in percent. I want to show my tool bar in a for loop. But the for loop doesn't go on only if i click away the userform. What i am doing wrong? For k = 1 To 300 pctcompl = k * 100 / 300 UserForm1.Text.Caption = pctcompl & "% Completed" UserForm1.Bar.Width = pctcompl * 2 DoEvents UserForm1.Show Next k

Add yes/no to objshell.popup in VBA

Below is my attempt at adding yes/no options to my objshell.popup, getting a type mismatch error, probably doing something wrong... got it from this website: http://www.informit.com/articles/article.aspx?p=1170490&seqNum=5 Public Sub ShowTable() Dim myData Dim myStr As String Dim x As Integer Dim myRange As Range Dim lastrow As Long Dim nsecond As Long Dim ws As Worksheet Call reviewME UserForm1.Show Set ws = Worksheets("New Lookups") lastrow = ws.Cells(Rows.Count, 262).End(xlUp).Ro

Using a loop to insert pictures to Word using Excel VBA

I'm trying to use a loop to shorten the last part of my VBA code. The current code I have is: Sub InsertPics() Dim WordApp as Object Dim WordDoc as Object Set WordApp = CreateObject("Word.Application") Set WordDoc = WordApp.Documents.Open("Testing.docx" WordApp.Visible = True Dim path() as Variant Dim i as Long Dim Pic as Variant path = Application.Transpose(Sheets("Selection").Range("N10:N24").Value) For i = LBound(ppath) To UBound(ppath) path(i) = Sheets("Output").Range("Directory"

Word 2016/VBA Highlight first use of each word from a word list

I am working on a macro for Word that accesses a separately saved doc file with a long word list of several pages. The word list doc is formatted like, FMS CPR Abc ...to separate each word by the line break. The macro needs to highlight the first use of each word from the list. Right now, the macro highlights every use of the word, and in addition, highlights that word when it's part of another word. For example, it highlights EZE in the word freeze, but it should only highlight when eze

Using VBA Sum Function to Transfer Data Between Sheets

I'm working on a looping data entry script. In some of the cells using the sum function is useful as the data could be in one of many adjacent cells, where knowing which cell it will be in is unpredictable, and all other cells in the range will predictably be zero. I start by naming my variables Dim file As Variant, I As Integer, Cval As String Input my folder and begin the loop and open the workbook file = Dir("C:\Users\test\") While (file <> "") Workbooks.Open (file) Next I creat

Vba using match funtion for 2 different workbooks

i have been assigned to use the .match function in vba, to compare 2 different columns in 2 different workbooks. here is my code so far.. how do i use the match function to my goal ? Sub Ob_match() Dim swb As Workbook, dwb As Workbook Dim sws As Worksheet, dws As Worksheet Dim oCell As Range, oMatch As Range Set swb = ActiveWorkbook Set sws = swb.Sheets("Item") Set dwb = Workbooks.Open(swb.Path & "\EPC_EndItem.xlsm", ReadOnly:=True) Set dws = dwb.Sheets("Data") If

Vba Sum function picking wrong values

I have activated "sheet2" but even then the sum function is taking up values from "sheet1" instead of "sheet2". Worksheets("sheet2").Activate Sheets("sheet2").Cells(fl1, nf) = Application.WorksheetFunction.Sum(Range(Cells(fl1, locrng1.Column), Cells(fl1, (nf - 1)))) Sheets("sheet2").Cells(fl1, of) = Sheets("Sheet2").Cells(fl1, nf) * Sheets("Sheet2").Cells(fl1, firstlevel1.Offset(0, 1).Column) Sheets("sheet2").Cells(fl1, rf) = Sheets("Sheet2").Cells(fl1, nf) * (1 - (Sheets("Sheet2").Cells(fl1, f

Creating "altered version of .Offset Function" (or "What would .Offset function look like if it were written in VBA?")

I am trying to write an altered variant of VBA's Offset Function, which when a cell is offset by an amount which would ordinarily fall outside of a specified range, instead takes the number of cells which exceed the range & "wraps" them around from the start of the range again. I have scoured the net for someone who has written an example of what the .Offset Function would look like if it were written in VBA code, but nobody has done it. Would anyone mind having a crack at what the .Of

VBA to copy sheets from all files in folder and copy it to master

I have a folder full of multiple excel files and all of the files have a specific sheet that i need to copy into my master. I need macro to open all files in that folder one by one and copy the specific sheet to the master file using the source file name as sheet name in the master workbook. Excel 2013. I tried searching online and have the following code: Option Explicit Sub test() Dim wkbDest As Workbook Dim wksDest As Worksheet Dim wkbSource As Workbook Dim wksSource As Worksheet Dim M

Vba Count years of increasing profits

I want to count how many years a company has increased the profit from now. So if the previous year had lower profits than this year it counts as 1 and goes one year back in past to count until there is one years profit higher than the year after that. So it must be a ongoing trend from this year (2017) so that it counts the years. (Years of increases should be 5 not 4, sorry!) In the picture it has to count until 2011 because there was a year(2012) without increasing profits relative to las

VBA text to columns isnt working on every row of a text file

I have a module in ThisWorkbook that is supposed to open .txt files from a location (it does so in another workbook), then do text to columns, then copy the individual columns and paste into ThisWorkbook. The .txt files have data in the format 23,100,100.65 on each row: three numbers separated by commas. Something strange is happening though, as the text to columns function works on data in some cells, but not others. Furthermore, it seems that when the .txt file opens in a workbook, some of t

Vba Only copy cells with data from Excel to Word

I need some further help developing my code. I have the basics down now with some earlier help but I am not sure on this next part. The code I am designing will run in a spreadsheet whereby the amount of rows used in a sheet will vary depending on the amount of data being used. (Due to nature of business and Norwegian Laws, I can't go into more details.) I'd like to have a Range of B5:B1000 as a standard range and only have cells containing data be auto filled into the template but I am unsur

Subscript out of range error in VBA?

I am trying to keep the values in the array. There is some 604 values it retrieves. This is giving me subscript out of range error. Can anyone help? PlCounter = 1 ReDim PlArray(1 To PlCounter) For Each plv In fs.PickListValues Debug.Print "entered into loop" Set pl = plv Debug.Print pl.Value If Len(pl.Value) = 0 Then Debug.Print " The length is null ..so assigining null" ReDim Preserve PlArray(1 To PlCounter) PlArray(PlCounter) = "NULL"

vba run other excel files macros

I have following code: Sub MacroRunner() Dim Nomefile As String, Nomefolder As String Nomefolder = ActiveWorkbook.Path Nomefile = Dir(Nomefolder & "\*.xlsb") Workbooks.Open (Nomefolder & "\" & Nomefile) ActiveWorkbook.Worksheets(2).Select Application.Run "Nomefile!listaIdprodotto" '<-------- "nomefile" variable not returned Application.DisplayAlerts = False ActiveWindow.Close End Sub The issue is in line marked by a left arrow; Excel doesn't return variable value making itse

Change range color depending on cell value VBA

I'm trying to have my code work so if the user types a "1" inside cell D19 then the background colors of cells F2 and F3 change to red and if they type a "2" the color changes to yellow. I can't see why my code isn't working, anything helps. Thanks. Sub ColorColumns() Dim r1 As Range, r2 As Range Set r1 = Range("D19") Set r2 = Range("F2:F3") If r1.Value = 1 Then r2.Interior.Color = vbRed If r1.Value = 2 Then r2.Interior.Color = vbYellow End Sub

Vba Excel 2016 crashes when using Debug.Print Tab() in Workbook_Open() event handler

Just something weird I noticed, tested on 2 computers with similar config (Windows 10 64-bit up-to-date with Excel 2016 32-bit), one was a clean install : Simply create a new .xlsm workbook, and place the following in the ThisWorkbook.cls class module: Private Sub Workbook_Open() Debug.Print Tab(10); "Hello!" End Sub Save. Close. Open (if opens in PROTECTED VIEW mode, Enable editing, close and re-open). Excel crashes. Minimal, Complete, Verifiable example. Now the big question: wh

Vba Scraping the table data from accuweather website

Hi I want to scrap the data from the table. I need all the weather information for all days click to see image Please check this link https://www.accuweather.com/en/in/bengaluru/204108/month/204108?view=table Source code: <tbody> <tr class="pre"> <th scope="row">Tue <time>5/1</time></th> <t

Vba How to get adresesses/values of ranges nested in a collection

I have a collection of ranges as following: Dim all_pivots_amounts As New Collection all_pivots_amounts.Add (corporate_amounts) all_pivots_amounts.Add (wealth_amounts) all_pivots_amounts.Add (institutional_amounts) all_pivots_amounts.Add (premium_amounts) all_pivots_amounts.Add (one_bank_one_bank_amounts) all_pivots_amounts.Add (one_bank_entrepreneurs_amounts) Debug.Print corporate_amounts.Address Debug.Print all_pivots_amounts(1).Address ' this line gives object required error I don't

Why am I unable to update Listbox's RowSource property using excel VBA?

What my userform looks like with the properties window: This is my code: With Worksheets("List of Accounts").ListObjects("ListofAccounts").ListColumns(1).Range Total_rows_Accounts = .Find(What:="*", _ After:=.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row End With If Total_rows_Accounts > 1 Then lbxCurrent.RowSource = "List of Accounts!A2:A4" End If What my worksheet looks

Vba My code to Create a Pivot works but the First row field does not work

I have been trying to create a pivot table in VBA and I partially succeeded even after creating the pivot cache and the pivot table the first rowFeild does not come up but the second rowFeild shows up even though I have numbered them as .position=1 and .position=2. Please can anyone help me as I am new to VBA coding and need help to fix this.. Where I have given the xlRowField to be "Natural Account" I have that column in my data and also the column "Natural Account Description" but still the c

Vba Trying to delete the first sheet in each excel file and then copying from each tab to the master workbook with the same tab names

Basically, what my macro is doing right now is going through a folder and picking each excel file and deleting the first tab which is named some_Accounts and then copy pasting data to the master workbook where the worksheet names match. I am getting an error on " If wsDst.Name = wsSrc.Name Then" Object variable not set or defined. Can someone shed a light on where this is going wrong? Here is my code Sub ProjectMacro() Dim wbDst As Workbook Dim wsDst As Worksheet Dim wbSrc

Vba Item.Restrict Function doesn't work when using like instead of =

I'm creating a macro that pulls e-mail attachments into a folder to be opened and copied, which works 100% perfect when the subject name is predefined, i.e. never changes. ' this works Set oOlInbFiltered = oOlInb.Items.Restrict("[Subject] = " & SubjectName) However, when I try to instead restrict for a preset beginning, say every email begins with 'aaaaa', it causes an automation error with the code below: ' this doesnt Set oOlInbFiltered = oOlInb.Items.Restrict("[Subject] like '" &

Vba Automatically saving attachments of certain emails

I'm trying to write a macro to automatically save certain reports we get via email at work. Another department has something very similar in place, so I can re-use a lot of their code. However, they couldn’t really explain to me what certain parts of the code do and googling it wasn't particularly helpful. If it helps, I’ll post the whole code, I left it out because a) I’d have to edit some things (names etc) and b) I don’t think it’s relevant to my problem. The point of the code is that on

VBA copy / paste

I have 80 machines, and 80 tabs in an excel workbook. On each tab there is a list of one month's dates in column (A) with an entry in a column (C) of how many accumulative hours the machine worked during the month. So, the daily increase is between 0 (if the machine was out of order) and 24. I am looking for a solution to automate the record in cases where the daily addition is zero hours. In that event the accumulated total from the previous day must be copied to the next day's cell in column

  1    2   3   4   5   6  ... 下一页 最后一页 共 285 页