Tagged Questions
0
votes
1answer
19 views
Range activation in a different worksheet
I was wondering if there is an alternative for the following code:
Dim Arr() as Variant
Arr = Range("A1:I511")
that refers to a specified worksheet. I tried the following
Dim Arr() as Variant
Arr ...
1
vote
2answers
36 views
VBA - Set Range for function Based on Header Value
Im fairly new to VBA so I need some help with this project I'm working on. I am trying to write a script that is easily scaled and extended so I am avoiding hard code values as much as possible. I ...
0
votes
4answers
40 views
Looping through cells, building a range in VBA
I am looking to loop through cells and build a range for a graph. My main issue is that I cannot figure out how to incorporate the 'i' into the range. Example:
Dim name As String
Dim newChart as ...
0
votes
0answers
10 views
Data Validation, Input Message, Application Defined Error for .InputTitle line in property definitions
I am trying to insert a Data Validation Input Message into cells as my program progresses through a row of cells. The message is a string collected from controls in my code (not included below).
I ...
0
votes
1answer
17 views
Accessing Certain Column in a Range
I am trying to access column 2 in a specific range, and then counting the cells with constants in them. Right now what I have is accessing the range, but not a specific column in the range, but ...
0
votes
1answer
43 views
Entire Range fills with only 1st item of array
I have two arrays, both with rows=intAllNumsRows. The first array (arrAllNums) is Public Dim'd as Integer and is 6 columns wide, i.e. arrAllNums(1 to intAllNumsRows, 1 to 6). The second ...
0
votes
1answer
11 views
Designating a range as a string in data validation code
I have a problem with a bit of code I am working on to designate a data validation set from a selection within another cell. For example, they choose Thomas, and every cell on a table that corresponds ...
0
votes
1answer
39 views
Merging dynamic ranges into one Range Excel or VBA
I am currently trying to take two ranges and combine them into one range. My ranges are dynamic because they change based off the date. For example, Suppose the two ranges I want to combine are A3:A10 ...
1
vote
1answer
27 views
Can't determine range rows count
I have 3 comboboxes placed on a worksheet named cbo1, cbo2, and cbo3. When the user clicks an element from one, my code retrieves data from a database and places the results in a range of cells below ...
-1
votes
1answer
26 views
How to paste a stored range to next free column and certain rows?
I've been stuck with this problem for a while and I'd really appreciate if somebody could help me.
So here's what I want to do with this macro.
Get data from two different ranges from sheet ...
-1
votes
0answers
34 views
VBA- Custom Function; #VALUE error; ranges from different sheets as variables for VLOOKUP
I am attempting to have values pulled using VLOOKUP by using parameters and variable. My goal is to pull data from the appropriate sheet based on the given parameters in my custom function. I am ...
0
votes
0answers
22 views
creating a column chart in vba with source data coming from user input
Hello and thanks for taking the time to look at my question. I'm trying to create a macro that will allow the user to create a column chart using data selected from a prompt. so this is the working ...
0
votes
1answer
34 views
Excel Range Reference
Let me preface this question by saying I am not super technical so much of my verbiage may seem obscure..
On sheet1 I have three seperate horizontal ranges of cells (3 seperate series of steps):
...
1
vote
1answer
23 views
Excel SortFields add then sort
Would you help me understand this snippset:
First, it seems that a sorting rule is added with
MainSheet.Sort.SortFields.Clear
For lI = 1 To vSortKeys(0, 1)
MainSheet.Sort.SortFields.Add ...
1
vote
1answer
13 views
offset formula for named range to use in chart?
I am trying to use a (dynamic) named range as the source for my chart.
I was hoping to be able to use 1 named range as the source for the chart, but so far, it is not working.
for example: my data ...
0
votes
1answer
23 views
Excel VBA Range Object
Why this throws me an error:
Dim Range_rng as Range
....
Range_rng = ActiveWorkbook.Names(1).RefersToRange
According to MSDN documentation RefersToRange returns the Range object referred to by a ...
0
votes
1answer
54 views
Userform Listbox, fill dynamically
I have Userform Listbox object. Everytime I start Userform select some options and then open second Userform with Listbox. According to options set in Userform 1 is adding items to Listbox. Listobox ...
2
votes
2answers
42 views
How do I iterate through a range of two columns and select both columns in a row at each step?
I am trying to automate the task of defining names. Specifically, I have two columns A and B, with B being dependent:
A B
Label1 Data1
Label2 Data2
Label3 Data3
I want to ...
0
votes
1answer
22 views
resizing range to enter array values into worksheet vba
If I wish to take an array and enter it into an Excel worksheet, how do I do that?
If I use my code below, they go into the wrong cell (G5 instead of F4) and cut off the last column and row of the ...
-1
votes
3answers
90 views
Pop up a message in vba
I am working on a file which in one sheet lets call it summary, I have formula that calculate the values which Ienter manually in other sheets, so I want to have an vba code to notify me by pop up ...
0
votes
0answers
24 views
Assistance Appreciated: VBA - Highlight columns 1 - 10 and activate column “J”
FYI: I am using Excel 2013.
Apologies in advance if this is not explained thoroughly enough - I'm no expert in VBA;
I do twice-weekly manual sales updating which equates to 10h/week. We must ...
0
votes
1answer
37 views
How do I stop old data from being updated by my macro?
I've been working on what is basically a register for sales. My spreadsheet records sales, profits etc and from that prints a receipt however, I'd like to keep my own record of this data to use for ...
0
votes
3answers
41 views
VBA field update
I have a sheet where I have 5 columns
Row OM MA HP D
----------------------------------
1 212 5454 4787 OM
----------------------------------
2 212 5454 4787 MA
...
0
votes
2answers
41 views
Find a table from a given cell excel-vba
I have a table which is located somewhere on a spreadsheet for example b3:f15 , and near it there is nothing ( empty cells ), I am trying to select this table and color it. Somehow my code doesn't ...
2
votes
1answer
43 views
Get range from a cell formatted as (A1:A15)
It might be a simple question but I couldn't find an answer yet
I have a cell which contains cells range as a text at the format of A1:A15.
I am trying to write a function to receive the actual range ...
2
votes
1answer
348 views
Using named range in VBA function for VLOOKUP
I have a the following on my worksheet:
A cell that shows a currency [in A1]
A range of cells (two columns, one for the currency, and the other for a corresponding commission percentage) [defined ...
1
vote
1answer
98 views
Using VBA to name ranges and create charts with those ranges
I'm new to VBA. I'm attempting to create over 500 xlClusteredColumn charts using two columns of information and I'd like to expedite the work. The first column contains names I'd like to use for named ...
1
vote
2answers
59 views
How can I edit a range variable without editing the cells on the worksheet
I have a userform which edits the values of certain tables within my workbook.
In the userform code I have this:
Public vTable As Range
Private Sub UserForm_Initialize()
Set vTable = ...
0
votes
1answer
165 views
VBA 2010 copying range cells between 2 sheets on the same workbook avoid loop
These lines are intended to copy a range of one line cells from a sheet (source) to another (target) on the same workbook.
The following code raises error 1004 - Application-defined or object-defined ...
0
votes
0answers
37 views
Strange behavior when calling WorksheetFunction.Average in VBA
I have a sub:
Sub TestrowSelectRetsRange()
Dim lastrow As Long
Dim i As Long
Dim n As Integer
Dim rng As Range
Dim avgReturn As Long
With Worksheets("RatiosCalc")
For i ...
0
votes
1answer
38 views
How to get the name of the cell using variables
I am trying to use excel VBA to enter a formula in a cell. I am doing it by, using the formula, Cells(x,y) = "='Additional Expenses'!" + CStr(Cname). Where Cname will the name of the cell(i,j), So ...
0
votes
1answer
33 views
Range and arrays in VBA converting whenever they want. What's going on?
The Range object in VBA is very strange.
Here's my practice code:
a = ActiveSheet.Range("A1:A3").Columns.Count
b = ActiveSheet.Range("A1:A3")
c = b.Columns.Count
a gives me 1, no problem.
b gives ...
0
votes
1answer
43 views
Split Address Column
I need to take an Excel Database that has an Address column setup like:
"Physical Address, Mailing Address, Suit#; City; ST; Zip" into columns split by the semi-colon.
Unfortunately I have data ...
0
votes
2answers
399 views
Run-time error '1004' Method 'Range' of object'_Global' failed
I have a macro that fills in some data at the DataCell location and a few other cells based on the information it finds on the master sheet. Once it finds a "blank" on the master sheet it stops. I ...
0
votes
1answer
44 views
VBA - Range: "Run-time error '91'
If the workbook isn't saved and closed and reopened,
I get the following error
"Run-time error '91': Object variable or With block not set"
I have the exact same code (only the name of the ...
1
vote
1answer
51 views
vlookup with VBA to PERSONAL.xlsb
I'm constantly having to use vlookup to find company names associated with a code. I already have an excel sheet containing the codes and corresponding company names. In order to ease my task I'm ...
0
votes
0answers
51 views
how do I specify a specific cell within a formula in visual basic
I want to total from C2 to one row up from the active cell. I am not sure how to term it within the formula.
ActiveCell.FormulaR1C1 = "minimum"
ActiveCell.Offset(1, 0).FormulaR1C1 = "maximum"
...
0
votes
0answers
42 views
Using range based on variables only
I'm trying to use range operator based on variables only but there is "application defined object defined error". I've tried this:
ThisWorkbook.Sheets(1).Range(Cells(lFirstRow, CInt(k)), ...
1
vote
1answer
691 views
Excel 2010 Macro for Creating a button that hides all blank columns in a range
I'm new with writing Macros and I'm completely stumped as to how to get my command button to only hide and unhide those columns that are blank within my range.
I've got my command button in place and ...
0
votes
0answers
199 views
Set a ComboBox list by reading a range and adding items only when they have satisfy a string value (VBA/Excel)
I was hoping someone could provide an answer to this VBA request.
I have a row (starting at A30) in Excel that is populated with string values "Time", "Extension", "Load", "Transverse Strain", "Axial ...
0
votes
0answers
83 views
Split range in half
I'm trying to create a subroutine to split a given range in two ranges of more or less the same size. Here is the best solution I've come up with, but it fails silently if half of the input range is ...
1
vote
1answer
176 views
VBA function to dynamically adapt chart data range
I am quite new with using VBA, especially to format chart on Excel but already run into an issue. I am trying to generate a Graph but the rows of the reference cells will vary depending on the data ...
0
votes
2answers
118 views
Excel find End of Range then Select Range and Copy
this may or may not have been asked yet.
I am trying to copy a Range of Cells from one worksheet to another using a macro. the problem I am having is that the Range I want to copy varies drastically ...
0
votes
2answers
539 views
Excel VBA selecting multiple dynamic ranges
I'm trying to selecting multiple dynamic range. Trying to use the union method and I'm getting Method 'Range' of 'object' Global Failed error on first Set line.
Dim LR As Long
LR = ...
0
votes
2answers
1k views
Subtracting ranges in VBA (Excel)
What I'm trying to do
I'm trying to write a function to subtract Excel ranges. It should take two input parameters: range A and range B. It should return a range object consisting of cells that are ...
0
votes
1answer
165 views
Populating an array of ranges using the ADDRESS function in Excel VBA
I have defined a two dimensional array of ranges that I wish to populate with references to a specific set of cells (the criteria for which are irrelevant). I have tried using the pseudo code below, ...
0
votes
1answer
218 views
Copy range of cells that contain only visible data
On Sheet1, I have three columns that need data entered in them. The three columns will always end in the same row, but the last row will change with each set of data.
Sheet2 takes information given ...
0
votes
1answer
108 views
Excel VBA: Error 1004 application-defined or object-defined error
I am having trouble debugging a code I wrote. It runns fine until a certain point and then stops and shows: runtime error 1004 application-defined or object-defined error.
I have not written VBA in a ...
0
votes
1answer
70 views
Return the name of a range.
I have a bunch of range names (mostly single cells) in a worksheet. When I change the cell named "Grade" I want its value to appear in another range called "GrdSrchSttng" (6 x 1 range) as the last ...
0
votes
1answer
490 views
Maximun value of each column from a dynamic range
Could someone help me with the below question...I'm quite stuck....my knowledge in that matter are too basic.
I have "sheet 1" with raw data. The number of rows and columns is always different. The ...
