All Questions
59 questions
0
votes
0
answers
24
views
Creating a dynamic range in excel using a variable starting cell
I am trying to create dynamic ranges to be used for excel charts.
I have the following range:
=OFFSET(O7,0,0,1,COUNTA($O$5:$X$5))
cell o7 is expected to change.
I have created the following formula to ...
- 55
0
votes
2
answers
248
views
Excel VBA; How to use Target.Offset from a range so only the cell offset from the last cell in the range is populated?
I am using the following code to populate a cell with the current date and time when any cell in a range is modified. I want the cell in column O to populate with the date when I modify any cell from ...
1
vote
2
answers
53
views
How do i select multiple, NON connected cells as a range with the offset function in VBA?
I have a column of data (column P). The data contains names every 12 cells, and has other data in between. I need to select only the cells containing names. The entire range of names and data moves ...
- 21
1
vote
1
answer
44
views
Vvertical offset % function to a SMA
is it possible to add a vertical offset in % function to a SMA?
My aim is to attach this SMA to a 1 period ATR.
Thanks
I'm not good with coding so I tried to use already existent indicators, which don'...
- 13
0
votes
1
answer
122
views
Excel dynamic formula with SUMPRODUCT and OFFSET
I have 3 columns in excel:
column P is a range of numbers starting from 1 until 11, then from 1 until 14 and so on, it is never the same number of rows
column W has numbers that are dependent on ...
1
vote
1
answer
2k
views
.Range(.Cells ... vs .Range( Cells... vs "Microsoft Learn" page
Surely the essence of my Question has come up before. What may be new about my Question is that it refers to what I hope is an error on this Microsoft.Learn page, and possibly my inane/deep questions. ...
- 4,273
0
votes
2
answers
221
views
Using .Offset with a fixed range
The script below works without
.Offset(1, -4).Resize(.Rows.Count - 1, 1).Value = "TEST"
The script takes a list of iPads and Tablets on a spreadsheet and finds the ones that don't have SIM ...
- 41
0
votes
1
answer
100
views
Loop range, but exclude some cells
I wrote below code that works.
I want to modify this
Set rng = Application.Intersect(Target, Me.Range("M30:AM53"))
If Not rng Is Nothing Then 'only loop though any cells in M30:AM53
To not ...
- 1
0
votes
2
answers
415
views
Excel- cumulative sum of clusters in dynamic range
I have the dynamic range A (A#) and I want to calculate the cumulative sum of each "subrange" in B. The result musst be something as shown in column B. In other words I need a dynamic ...
- 101
2
votes
1
answer
589
views
Excel VBA: Loop and finding multiple values in a range then copying the offset cells in the same range in a categorized list in another worksheet
I am in need of help with looping through a range (In this case Column "C") and finding 5 specific words (Sort, Set, Shine, Standardize, and Sustain) and then offsetting one row below the ...
- 21
0
votes
1
answer
109
views
excel vba moving the offset to start of the range if exist (functional way of writing)
Goal is to identify the start of the region value and it will be used in offset.
Input will be dynamic
Row length is not fixed.
Only criteria is region begin and end, but it may exist or not (see the ...
- 65
1
vote
0
answers
93
views
VBA to return the RANGE identified in OFFSET formula
I'm desperately chasing circular reference errors in a very large workbook.
One of the culprits could well be within one or more OFFSETs which I use extensively.
I'm trying to write a VBA script which ...
- 11
1
vote
2
answers
79
views
Excel - Test if two consecutive digits in the range's address are the same
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 ......
0
votes
0
answers
148
views
VBA function for Upside/Downside Capture
apologies for my ignorance, I'm brand new to VBA - I'm sure this is a simple problem...
I'm trying to write a fn. for up/down side capture in VBA. This is the problem:
There are two columns. One has ...
1
vote
1
answer
712
views
How to reference all cells from a cell to the very bottom and right in Google Sheets?
I know you can reference cells in these ways:
Address
Explanation
A1
The cell at A1
A2:A
All rows in column A starting from row 2
B1:1
All columns in row 1 starting from column B
What I can't ...
- 5,771
-1
votes
2
answers
409
views
How do you average repeating blocks of cells in Excel?
I am trying to figure out how to average 4 x 4 groups of cells in my spreadsheet across a very large data set. I've tried using OFFSET with a cell range (e.g. B2:E5) but I haven't had success (I don't ...
1
vote
1
answer
3k
views
Find column number of a specific text in vba (excel) and use its address in range function with a row that I will specify
Sub try()
Dim SValue As Range
With Range("A1:Z100")
Set SValue = .Find(What:="FF", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not SValue Is ...
0
votes
1
answer
420
views
How to calculate range() for the graph in Gremlin pagination query?
How to calculate range() for the graph in Gremlin pagination query with input of page number and limit.
- 9,461
0
votes
1
answer
91
views
VBA: How to move value and offset for each subsequent value
I'm trying to paste each value in a loop on a differerent worksheet to a certain cell and then offset by 5 rows for each value I'm moving.
Dim counter As Variant, carModel As Variant
Dim theRange As ...
- 37
1
vote
2
answers
248
views
Using an offset and range inside a loop
I know this will be a stupid simple answer, but it's killing me right now...
For Each cell In rng2
If cell.Offset(0, -13) And cell.Offset(0, -12).Value <> "" Then
cell....
- 15
0
votes
0
answers
192
views
Excel Listobject resize and offset range cell
I have a Listobject in a particular sheet:
Dim ftstbl As ListObject
Set ftstbl = ThisWorkbook.Worksheets("employees").ListObjects(1)
I want to refer a range aoutside of the listobject with ...
- 3,413
0
votes
1
answer
426
views
When I use range.offset in VBA, I get error code 1004 application defined object or object defined error due to synatx error
I am trying to use the offset function and cannot figure out the problem with my synatx.
I copied this code from another question about offset on this website.
I want to look for a string in column X (...
1
vote
1
answer
451
views
Excel: Dynamic Ranges with periodically skipped cells (to create Dynamic Charts)
I struggle with Excel in order to generate a water consumption monitoring each month.
Objective: Create a graph in which data are automatically added each month.
Issue: My table is not so common. ...
- 13
1
vote
1
answer
38
views
Conditional value entered in specific cell in a range, unlocks another specific cell in another range (VBA, Range Offset)
I am VERY new to VBA, but trying my best to catch on, reading everything I can, and testing as much as I can. I simply cannot figure this out after days of trying and I am begging for help, please!
...
- 13
0
votes
0
answers
52
views
Dynamically Change Chart Rate Based on Option Button Selection
I have been trying to dynamically change the range of a stacked bar chart to adapt to different selections of three option buttons. My table is arranged from J1:M14. The top line contains the ...
- 911
0
votes
1
answer
79
views
How can I create a dynamic range in two directions? I want to use it for a minsif formula
How can I create a dynamic range in two directions? Offset only goes one direction. I want to use it for a minsif formula that will start at cell 2 and copy down but only look at 40 cells at a time.
...
- 5
0
votes
1
answer
804
views
Dynamic range based on values in another cell
I will lay out the question I currently have. I have an excel sheet in which column A is Date-type data, Column F is returns data, say returns of S&P500 (for examples sake). I have two cells, say ...
- 129
0
votes
2
answers
323
views
dynamic named range using offset/counta but with a twist
im creating a auto-updating pivot table from a dynamic named range that is only 1 column. the range in question has data below it and above it. is it possible to still have a dynamic named range using ...
- 51
0
votes
2
answers
298
views
Issue with using WorksheetFunction.Substitute in combination with Offset
I need to write a function to carry out a number of substitutions into a string, based on looking up values in the sheet.
My intention is to iterate over a list of substitution pairs in the sheet and ...
- 305
-1
votes
1
answer
52
views
startRange.offset - object does not support this property or method
Receiving an error when I run the code below. This worked last month, just seemed to stop working since I performed an update on Octobers data.
The script should grab data from Derek_Calc, which is a ...
0
votes
3
answers
5k
views
Select Range within Row of Active Cell
I've seen several other questions similar to mine and I've tried several different solutions but I am still getting strange results. My code finds a value in another workbook in Column AA, then I ...
- 1,927
0
votes
1
answer
317
views
Copy values from Range and paste each one with different given (row) offset in another sheet
First of all I would like to introduce myself. Iam Miguel and I recently started to learn VBA in order to improve and save time at work. I am familiar with formulas, all types, but when turning to VBA ...
- 3
0
votes
1
answer
1k
views
VBA Excel. Edit a range based on an offset from a named cell
I don't know if this is possible, but it feels like it should be.
I have a cell with a defined name: "Data_A" (It is a single cell, ie a 1x1 range).
I want to write an array (N x M) to the cells ...
- 115
1
vote
0
answers
932
views
ClearContents and Shift Data Below up without deleting rows/cells
I automating a spreadsheet cleaning tools that removes data from the spreadsheet if certain values are in a particular column. Originally, it deleted entire rows of data if the specific value is ...
- 177
0
votes
1
answer
2k
views
Correct syntax for Offset and merged cells - VBA
I am having trouble with the correct syntax for using offset with merged cells. For example, in column A, row 2 to 5 (A2:A5) is a merged cell. I'm wanting to use offset to get the range of the column ...
- 15
3
votes
1
answer
1k
views
Get click range relative to parent element
So I have this problem where I need to get the clicked spot offset relative to the parent element.
Lets assume I have html rendered like that:
<div>
able to allocate many IPs.
&...
- 83
0
votes
1
answer
482
views
Google sheet Offset 4th parameter
In Excel I am able to use like so:
OFFSET(RangeName, 0,0,,1)
this will get all values of Column 0 (first column) in my multi-column range.
But when i try to do this in Google Sheet, error comes up:
...
- 7,588
2
votes
1
answer
4k
views
VBA trying to use range.offset with a cell address held in a named variable
I am trying to get a small matrix of data from one workbook and paste it into ThisWorkbook using offset from a cell reference. The cell reference changes though as I'm cycling down a range looking for ...
- 133
0
votes
1
answer
93
views
Excel macro to paste data into column X
I am looking for a macro to paste some data onto a moving range. I already have a cell that tells me the number of the next non empty column and this is the code I currently use:
Dim OpenFileName As ...
- 1
0
votes
1
answer
484
views
VBA Excel range offset
I've got this:
Public Function Gegevens_Ophalen(ByVal ParameterRow As Integer, ByVal KolomLetterSOM As String, ByVal sheetname As String, ByVal Rij As Integer) As Single
Dim WB1 As Workbook
Dim WB2 ...
- 123
1
vote
1
answer
91
views
Count number of unbroken 'strings' of numbers across a row in VBA Function
I have been battling this one for days, and am clearly missing something. It has been a while since I last used VBA and I am struggling to remember.
Looking to find the number of 'tours' or ...
- 13
0
votes
1
answer
78
views
Splitting a single column of data into multiple columns
So I am pretty new to using VBA in excel. So please bear with my ignorance.
I have a column of depths in meters which correspond to an interval. I want to paste the 4 depths under each interval off ...
- 1
1
vote
2
answers
2k
views
How to extract a range of elements between two specified keys? [duplicate]
I have two arrays, the first one is:
Array (
[0] => Mar
[1] => Jun
)
and the second one is:
Array (
[Jan] => January
[Feb] => February
[Mar] => March
[Apr] => April
...
- 403
0
votes
1
answer
108
views
FindNext Won't Work With Multiple Dynamic Ranges
What I'm trying to do is add some cell formatting underneath each of my dynamic ranges. I figured I could use a Find and FindNext but the code I've come up with only works for the 1st Dynamic Range. I ...
- 85
1
vote
2
answers
187
views
Refer to Named Range from Cell Value in For Loop
Good afternoon all,
I'm a bit rusty on my VBA since I have moved onto Programming in other languages, so I was hoping someone would be able to help me.
What I am Tying to Do
I have two sheets. One ...
- 138
0
votes
1
answer
6k
views
Excel VBA: Using .Range.Offset property on a range variable returns unexpected values
I have given Stack Exchange and Google a good search and I can't find the exact answer I'm looking for.
I'm having trouble understanding the values returned from using RangeVariable.Range.Offset, NOT ...
- 3
0
votes
2
answers
2k
views
C++ vector iterator + offset out of range
I have c++ code that compiles on Xcode, but when I try to compile it on Visual Studio 2015 I get an error that iterator offset is out of range. Can someone try to help to figure this out:
typename ...
- 535
1
vote
2
answers
23k
views
VBA move selected range up / down (with offset?)
I am building a tool where user-selected cell contents is moved around with arrow shapes.
The code below works great to move 1 or more group of adjacent cells down.
However, reversing the code ...
- 25
1
vote
0
answers
1k
views
PyQtGraph, make two plots match eachothers visual range (produces offset)?
I am trying to make two plots have the same visual range for X and need both to change when one or the other is changed.
Here is some bare minimum Python code showing what I am trying to do:
(Only ...
- 1,848
0
votes
1
answer
75
views
Multiple dynamic ranges for printing
So I got this table:
I need Excel to print different ranges on different sheet of papers. Ranges will always be different. I need A1 (range A2:B3) routes to print on one page and A2 (range A4:B5) ...
- 460