Issue I need some help trying to figure out why this code is working the way it is. There are dates where it would say that there is a conflict when it shouldn’t be. It would be great it I
Continue readingTag: vba
VBA: How can I fill in cells in one column based on the collective information of iterative ranges?
Issue I need to fill in Col H (see red text in image for an example) as follows: There are 3 subjects listed (separated by grey background) (Col C for number) Each subject has multiple data points (one per row-Col
Continue readingSorting data by multiple columns with zeros and blanks
Issue I’m trying to write a code that sorts two columns in a worksheet but having difficulties due to there being zeros and blanks. I need to sort by date (earliest to oldest), then sort the data in terms of
Continue readingChange cloned object without changing the original object
Issue Is it possible to copy an object in VBA so that changes of the clone object do not affect the original object? For example: Dim clone_object As Variant Set clone_object = some_object some_object.Left = 0 clone_object.Left = 666 ‘I
Continue readingcode causing a 1004 error and im not sure why
Issue So i have this string of code that should let me auto highlight rows and columns of active cells… The catch is it is popping a 1004 error and I’m not sure why. Possibly in the syntax? It flags
Continue readingLet the user to give star rating
Issue I wonder if anyone could help me with a star rating of a specific row in Excel not based on any give number or any information, just based on the user idea, some thing like most websites use to
Continue readingType of variable scoping in vba language
Issue I try to understand the VBA scope type, it’s impossible to make this such of thing in VBA, but it’s possible in other language (java,scala,etc): public sub try() dim myVar as String myvar = “hello world” Call displayVar() end
Continue readingIs scoping broken in VBA?
Issue Say you have this code in a module called Module1: Option Explicit Private Type TSomething Foo As Integer Bar As Integer End Type Public Something As TSomething In equivalent C# code if you made the Something field public, the
Continue readingMerge Function with Range and Cells
Issue I am trying to merge cells in a loop: WS1.Range(.Cells(2, 2 + ((i – 1) * 9)), .Cells(2, 2 + ((i – 1) * 9) + 5)).Merge but is not working; what is the correct way to do that?
Continue readingHow do I use Change(byVal Target as Range) to check/change cell color
Issue My primary data entry is a Worksheet named "Master". I want to check in Range A2:A1000 when a word is entered. If it is "CBI", "Fire", "InCase" or "LEA" nothing needs to happen in Column I (Offset(0, 8)) as
Continue readingVBA method 'range of object' _Worksheet failed variables used in range declaration
Issue I am getting the following error message: Method range of object _worksheet failed when trying to select a range in excel using variables as range length. Below is a snippet of my code: Private Function copyAmount(startRange As Integer, endRange
Continue readingAutomatically inserting Colon (:) in multiple columns under the Options Explicit
Issue I was looking for a code to automatically insert the ‘:’ (colon) into the columns R and S, W and X, and found code that I thought I could customise to my needs, but I am facing two issues:
Continue readingSelect and Copy multiple ranges with VBA
Issue I want to copy multiple range to another workbook. I have the code below. How can I replace the number 1000 by iLastRow iLastRow = Sh.Range(“B” & Rows.Count).End(xlUp).Row sh.Range(“A3:AG1000, AL3:EJ1000”).Select Selection.Copy Solution Try the code below, explanation inside the
Continue readingDynamic graph using VBA
Issue I have the following table of SKU and Volume (E4:F10) SKU Volume a 10 b 20 c 30 d 40 e 50 f 60 I need to make a graph out of this data, with the number of rows
Continue readingWhen cell from range A exists in Range B then copy both into rows below tables
Issue I have two small tables. First one contains 3 columns and 5 rows. Second one contains 4 columns and 5 rows. When cell value from first table (column 3) is equal to cell value from second (table column 3,4)
Continue readingHow to assign a value to a Range created with Cells() in VBA
Issue I want to assign a value to multiple cells using a Range. I must define the range using variables, that’s why I use the Cells() parameters, but when I create a Range object with Cells, the compilator returns a
Continue readingvlookup and return entire cell including formatting from non active workbook
Issue I’m looking to vlookup a value from a tableArray on a different workbook and return the value and entire cells content. This SO answer is nearly what I’m looking for. If I amend this links copyFormatting routine (Private Sub
Continue readingVBA Find if an array (made up of range) contains a specific value
Issue I’m trying to write a code which takes a specific predefined, hardcoded value and a range made up of values from 3rd to last column with value (just one row) and sees if my array contains that specific item.
Continue readingSelect Headings of selection(s) to be union with the selection(s) itself?
Issue By using manual selection(s) , I copy range(s) from workbook to another workbook. But, how to select the headings of this selection(s) to be union with the selection itself , to fulfill copy and paste in one shot. Headings
Continue readingCopy non-contiguous selection from workbook to another workbook
Issue I copy data from workbook to another workbook ,copying of single contiguous selection works without problem. But, If I copied two selections (using CTRL) , even adjacent , nothing copied to the destination workbook (with no error raised). How
Continue readingChange value of another cell via a User Defined Function
Issue The Function TotalHours() is designed to accept two ranges. The first range should usually be one row by seven columns (seven days of the week) and the second range is a single cell to accept a value. I have
Continue readingHow to import range as array, from Excel into Word document
Issue I’m working on a VBA Word Macro to create a document with personalized headers. I have a spreadsheet with the text to be inserted in the Headers (every row shall be inserted on an individual page). To do so,
Continue readingCopy Excel range as picture to Outlook mail under text in body
Issue I would like to copy a range from protected Excel sheet and paste it into Outlook as a picture. My code is pasting the text then the picture, but at the same time deleting the text. How can I
Continue readingCopy Multiple Non-Adjacent Columns To Array
Issue I’m trying to copy multiple non-adjacent (non-contiguous) excel columns to an array but it’s not working. Below is what I’ve tried… Public Function Test() Dim sh As Worksheet: Set sh = Application.Sheets(“MyWorksheet”) Dim lr As Long: lr = sh.Cells(sh.Rows.Count,
Continue readingRange Selection not getting range I need
Issue I am still very new to VBA. I have a workbook with multiple sheets that is updated monthly. On 2 of the sheets, I need to copy the last 5 columns that contain formulas, copy them (with number formatting)
Continue readingDynamic update of chart-series using a dynamic range
Issue I am in need for some coding assistance, as I’m fairly new to coding and do not understand what I am doing (writing) wrong. This is the first time I am trying to update charts using vba and would
Continue readingRange(Cell.Find("Price tag"), Range(Cells.Find("Price tag")).End(xlDown))
Issue I want to find where ‘price tag’ is on the sheet, and follow that column to select all way down. I have wrote Range(Cells.Find("Price tag"), Range(Cells.Find("Price Tag")).End(xlDown)) but I got [range method of object _global failed] message. What is
Continue readingMirroring Range of Cells in Between Sheets
Issue Sheet1 Private Sub Worksheet_change(ByVal Target As Range) If Not Intersect(Target, Range("B93")) Is Nothing Then If Target = Range("B93") Then Sheets("Sheet2").Range("A1").Value = Target.Value End If End If End Sub Sheet2 Private Sub Worksheet_change(ByVal Target As Range) If Not Intersect(Target, Range("A1"))
Continue readingCopy paste range of cells for every row called xxxx
Issue I have this worksheet that I want to move (by copy and paste) range of weeks as well as every row called Plan. Below is a simple Script that I’m using but I would like to create of loop
Continue readingNeed to highlight array if condition is met. But How?
Issue I have a cell with data to filter with specific text (pre-filtered). just want to highlight specific cells with the cell value that meet the array of special cells. Sub EmailDataPrep() Dim r As Range Dim lastrow As Long
Continue readingHow to check for occurences of a range in a string?
Issue So I have a vertical range of a few words, and I have in a cell a sentence in a form of a string. I was wondering how I could write some code in order to check for the
Continue readingThis For Loop works for the first sheet, but not for the subsequent sheets
Issue Dim a As Integer Dim RNG As Range For i = 1 To 24 Sheets("IntangibleAssets").Select Set RNG = wksht.Range("F10:F63") Selection.Copy Range("E10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ‘ Move onto next tab, macro starts again from the beginning
Continue readingVBA Application/object defined error when outputting array to .Cell declared range
Issue I have an issue where I get the ‘application-defined or object defined error’ when trying to output an array into a range. Worksheets("Log").Range(.Cells(MnthCount + 4, 6), .Cells(MnthCount + 4, MnthDayCount + 5)) = FrstLtr That is the code, the
Continue readingexcel vba select negative range
Issue Sort "Column B" <== done with the following code: ‘ sorts %change from cell B2 to the end of the line Range("B2", Range("B2").End(xlDown)).Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo Data: A B C 1 8 2 2 -2 4 3 3 -1
Continue readingcolour cell based on value (date) on another sheet
Issue I have several dates on the Column D of sheet 2. I want to search the first row of sheet 1 and if the same date is found colour the cells but can’t seem to make it work. I
Continue readingExcluding rows or columns from Checkbox macro
Issue So, I have based a lot of functions on a sheet on checkboxes. It grew beyond the original scope, and now I needed to add a few more control textboxes The problem is, I wrote a script which would
Continue readingexcel vba select range between two numbers
Issue C column is sorted from the input data From the sorted output, the selected range should be with in -0.25 to +0.25 How to add more than one criteria in COUNTIF function or its similar function? Original syntax: .Resize(Application.CountIf(.Cells,
Continue readingMacro only working in one sheet and not the others despite qualifying ranges in the code
Issue I have a button assigned to this macro in my "Output" tab which works well. When I copy it to other tabs it gives me an "runtime error 1004 application-defined or object-defined error" at the "Worksheets("Output").Range("B5"….." line. It’s my
Continue readingCreating an Array from a Range in VBA
Issue I’m having a seemingly basic problem but can’t find any resources addressing it. Simply put, I just want to load the contents of a Range of cells (all one column) into an Array. I am able to accomplish this
Continue readingSubscript out of range with copy a range in one workbook to another workbook
Issue I have a range of data in a workbook that I would like to copy to the active workbook. The code looks simple, but I am getting a subscript out of range error when I run the code. What
Continue readingHow do you find the row a value entered from inputbox is in, and then use it to define .select data range?
Issue I am having trouble getting the following block of code to perform. I keep getting the 400 error and am very new to this. Any suggestions based on the code below would be highly appreciated. I have a feeling
Continue readingexcel vba automatic formulas according to my tables sizes
Issue I need to know if the is anyway to automate the range formulas used according to the columns. This was my original code: Sub Formulas_new_sheet() ‘ Cambiar los rangos acorde al numero de filas usadas ‘ Formula Costo unitario
Continue readingHow to Automate my Manual Selection Process in VBA
Issue I have a manual selection process that I have tried but failed to automate, so I am reaching out for help. I have attached an image of my Excel sheet as a visual guide when reading my process. Excel
Continue readingExcel – Test if two consecutive digits in the range's address are the same
Issue I want to offset a range if the numerical part of a range’s address can be divided by 11. (A11, A22, A33, and so forth). Take a range in a given sheet, for example Range("A2"). Could I do …
Continue readingTrying to calculate a 1hr range within the last 12hrs of a range with VBA
Issue So I am trying to calculate a test temp for 1hr from the last 12 hours of data. Sometimes this data can be several days some times it can be only 24hrs. The statements seem to select the 1hr
Continue readingVBA Type mismatch using RANGE of address for a single cell
Issue Trying to pass a range to an array. If the range is a single cell (e.g "A1") it returns a type mismatch (error 13). It works only if the range passed to the function is at least 2 cells e.g
Continue readingCopy a range to a variable, then offset that range variable and save the value to another variable
Issue So I am trying to search for a cell range with a specific string, then copy the contents of the cell 3 spaces under it to save to another spreadsheet. The code I have so far does this, but
Continue readingVBA – How to Clear Cell Contents across Multiple Columns if Value not Contained
Issue I have a macro that creates data in a range of columns starting from column D onwards with n=iCount, e.g. if iCount=4, then the columns are D,E,F,G. Throughout all these columns I would now like to clear cell contents
Continue readingHow do I convert an address back to a range in VBA?
Issue I have an input box that asks the user to select a cell, storing this as a range. Then, this range will be converted to an address (string) so the !worksheet isn’t also saved. A For loop will cycle
Continue readingVBA paste range
Issue I would like to copy a range and paste it into another spreadsheet. The following code below gets the copies, but does not paste: Sub Normalize() Dim Ticker As Range Sheets("Sheet1").Activate Set Ticker = Range(Cells(2, 1), Cells(65, 1)) Ticker.Copy
Continue readingSelect range in a particular sheet in excel vba
Issue I have a question about selecting a range in a particular sheet using excel vba. I don’t know why the following is not working: Thisworkbook.Sheets(“N&A”).Range(“B4:F16”).select However this works: Thisworkbook.Sheets(“N&A”).Activate ActiveSheet.Range(“B4:F16″).Select The VBA code is programmed on “N&A” sheet. Could
Continue readingTrying to make a macro to copy and paste ranges from several sheets to one sheet
Issue I am creating a macro that i want to be able to use to Copy and Paste ranges from several worksheets to one sheet. I know the range that the range i need to copy will start in C3
Continue readingMerge rows across defined set of columns in VBA
Issue I want to merge a group of rows (keeping all the contents of each row) and then move on to the next column in a defined cell range. I’m trying to expand this Excel VBA code (https://excelchamps.com/vba/merge/) to a
Continue readingVBA MIN and MAX function always returning 0
Issue Hello I am trying to get the MIN and MAX values from the array and it always returns "0" despite anything. My code: Dim MachineCapacitySmallestArray() As Variant MachineCapacitySmallestArray = thisworkbook.worksheets(1).range("C25:D25") SmallestCapacity = Application.Min(MachineCapacitySmallestArray) in range I have natural numbers
Continue reading"run time error 91 object variable or with block variable not set" working with ranges
Issue I’m a VBA beginner. I get a ‘run time error 91 object variable or with block variable not set’ trying to run the following code. Here is what I’m trying to do: Select all data in my worksheet Name
Continue readingLoop is copying the columns in the wrong ranges
Issue I have made this code to create an output sheet where columns from different sheets are sorted by header name and pasted one after the other. For some reason, it is not pasting the columns underneath each other, but
Continue readingSelecting a range based on values of two other columns
Issue I have two columns with the range O6:P that evaluate the row of data in J and display Pass or Fail. The results in O and in P are not always the same. One can display Passing and the
Continue readingEdit 14 day weather forecast Excel VBA to include precipitation
Issue I found the code below which works nicely and I think I can repurpose it for my needs, but does not include the precipitation. I’m relatively new to HTML so having trouble understanding what each line of code’s purpose
Continue readingWorking code gives error when run on any other PC
Issue I have working code that requests information from a website. When I send the file to another PC and run the code, I get: "Run-time error’91’: Object variable or With block variable not set" I ensured: Macro security levels
Continue readingWorking code gives error when run on any other PC
Issue I have working code that requests information from a website. When I send the file to another PC and run the code, I get: "Run-time error’91’: Object variable or With block variable not set" I ensured: Macro security levels
Continue readingWorking code gives error when run on any other PC
Issue I have working code that requests information from a website. When I send the file to another PC and run the code, I get: "Run-time error’91’: Object variable or With block variable not set" I ensured: Macro security levels
Continue readingMissing Image in Signature File
Issue I’m working on a short script, where I generate mails based on a Access DB, using the Outlook.MailItem with htmlbody. Everything went fine, till it comes to the signature integration part at the very end of my mails. A
Continue readingOpen a webpage with vba and click on the "active directory" button so you can log in
Issue so I want to open a web page directly from excel vba where I need to click on the "active directory" button which has no ID, this button uses the windows credentials to automatically log so I just need
Continue reading