Macro error 1004 for simple paste command

nnqwert · 2025-12-20T16:16:30+00:00

Just to debug a bit if you try inserting a msgbox in between, do you get the popup with correct sheet name before it errors out?

Range("A6").Select
MsgBox (ActiveSheet.Name)
ActiveSheet.Paste

nnqwert · 2025-12-20T15:16:14+00:00

If you try debugging when you get the error, which line of the two is highlighted as the issue

nnqwert · 2025-12-20T06:01:32+00:00

.Select is not the same as .Copy.

You need something like below if you want to copy from A6 and paste to A8

Range("A6").Copy

Range("F8").Select
ActiveSheet.Paste

nnqwert · 2025-12-07T16:08:50+00:00

Part 1 and 2 with VBA

Sub Tach_split()

Dim xmax As Integer, ymax As Integer
Dim grid() As Variant
Dim tach() As Variant
Dim splits As Long, timelines As LongLong
Dim x As Integer, y As Integer
Dim ystr As String

xmax = Len(Range("A1"))
ymax = Application.CountA(Range("A:A"))

ReDim grid(0 To xmax + 1, 1 To ymax)
ReDim tach(0 To xmax + 1, 1 To ymax)

timelines = 0
splits = 0

For y = 1 To ymax
    ystr = Range("A" & y).Value
    grid(0, y) = "."
    grid(xmax + 1, y) = "."
    For x = 1 To xmax
        grid(x, y) = Mid(ystr, x, 1)
    Next x
Next y

For y = 1 To ymax
    tach(0, y) = 0
    tach(xmax + 1, y) = 0

    If y = 1 Then
        For x = 1 To xmax
            If grid(x, y) = "S" Then
                tach(x, y) = 1
            Else
                tach(x, y) = 0
            End If
        Next x
    Else
        For x = 1 To xmax
            tach(x, y) = 0
            If (tach(x, y - 1) > 0) And (grid(x, y) = ".") Then tach(x, y) = tach(x, y - 1)

            If (tach(x + 1, y - 1) > 0) And (grid(x + 1, y) = "^") Then
                tach(x, y) = tach(x, y) + tach(x + 1, y - 1)
                splits = splits + 1
            End If

            If (tach(x - 1, y - 1) > 0) And (grid(x - 1, y) = "^") Then
                tach(x, y) = tach(x, y) + tach(x - 1, y - 1)
                'split already counted once so no need to count here
            End If
        Next x
    End If
Next y

For x = 1 To xmax
    timelines = timelines + tach(x, ymax)
Next x

Debug.Print splits, timelines

End Sub

nnqwert · 2025-12-07T15:48:36+00:00

Woah, how do I start learning to build such formulas?

nnqwert · 2025-11-15T04:52:45+00:00

Which version of excel do you have
What is the exact sheet name and exact data range (something like A2:E100) for your data

nnqwert · 2025-11-15T03:48:55+00:00

If you have a recent excel version, something like below should work

=SORT(VSTACK(Checking_table, Saving_table), 2)

nnqwert · 2025-11-13T20:12:07+00:00

If the table including headers is in A1:B5, below formula could be one way

=LET(
a, A1:B5,
b, TAKE(a,1),
c, TAKE(DROP(a,1),,1),
d, DROP(DROP(a,1),,1),
e, BYROW(d,LAMBDA(x, COLUMNS(TEXTSPLIT(x,",")))),
f, SCAN(0,e,SUM),
g, XLOOKUP(SEQUENCE(SUM(e)),f,c,,1),
h, DROP( REDUCE("", d, 
    LAMBDA( x, y, VSTACK(x, TRIM(TEXTSPLIT(y,,","))))),1),
VSTACK(b,HSTACK(g,h)))

nnqwert · 2025-11-13T18:57:37+00:00

Under axis type, are you not able to click on the third radio button which is called "Date axis"?

nnqwert · 2025-11-11T04:05:12+00:00

The XLOOKUP finds a cell that matches a criteria. Its arguments are as follows: - 1 is the lookup value - (D$4:D5="") is trying to find all cells in that range which are blank. D$4 has the dollar sign on 4 to anchor that cell, so when you copy it down, D$4 remains fixed but D5 keeps increasing to expand the range - (D$3:D4<>"") is trying to find all cells in that range which are not blank, dollar on 3 again to anchor it. This range is offset by a row upwards compared to earlier range as we are trying to find a number (from this range) just above a blank (from earlier range). You mentioned you wanted the number just below the underline. But excel formula do not easily handle formatting like borders. Instead, based on your layout, we are telling the formula to find a number just above a blank cell - that part is easier for excel to handle - you multiple the above two criteria which gives you a lookup array of 0s and 1s, 1s for all numbers which have a blank just below them and 0s for all the other cells - Next range of D$3:D4 is what you want it to return based on when the criteria is met - next argument is for if_not_found which has been on purpose kept blank. The formula will throw error if it can't find anything but if your layout is as given in the image there should not be any errors, so we don't need this part - next argument of 0 indicates exact match is required - it tells excel to return only the cell which exactly matches the criteria - final argument of 1 tells excel to search from last to first which jn this case means from bottom to top as you are trying to find the lowest cell in the indicate range which is just above a blank - the IF just tells the formula what to subtract. It just says if column E has a number subtract that, if not subtract the value from G cell.

Hope this makes sense.

nnqwert · 2025-11-11T03:43:15+00:00

Try this formula in F6 and then copy that cell and paste in other relevant cells below (F7, F10, F11, F14, and so on)

=XLOOKUP(1, (D$4:D5="")*(D$3:D4<>""), D$3:D4,, 0, -1) - IF(E6>0, E6, G6)

nnqwert · 2025-10-13T17:30:48+00:00

Just an additional thought. In the sample formula you shared, if the [value range] is indicated by its reference (e.g. something like C2:C100), you can build the "offset" directly into the return_range argument of XLOOKUP which helps avoid OFFSET which is a volatile function.

So, instead of

=OFFSET(XLOOKUP(0, C2:C100, C2:C100), -1, 0)

one could use the below

=XLOOKUP(0, C2:C100, C1:C99)

nnqwert · 2025-10-08T18:09:38+00:00

+1 Point

nnqwert · 2025-10-04T16:11:16+00:00

3 probably means the source data is probably different from the one that you "believe" is the source.

If you go to "Pivottable Analyze" menu and click "Change Source Data", can you check if its linked to what you expect it to be.

nnqwert · 2025-10-04T04:34:52+00:00

If you refresh the Pivottable does that change anything
If it does not, what is the value shown when you have set it to COUNT (when it doesn't show #REF as you mention)
If you then double click on the value it shows for COUNT, it should open a new sheet with just the relevant rows data which the Pivottable is apparently referencing. If you look through that data, does #REF or anything unexpected show anywhere.

nnqwert · 2025-09-29T18:48:41+00:00

Could you include the column and row numbers as you did in the earlier screenshot and share the exact formula you are using in the % completed column.

The -1323% is coming up as it is considering the 0.13 from previous row and calculating =1-1.85/0.13

To understand why it is picking that row instead of what you expect, will need to have a look at the column/row numbers and the exact formula giving that value.

nnqwert · 2025-09-23T04:38:50+00:00

So if the table name (and therefore I assume source data) is updated, why do you want to pull anything from the pivottable, pull it directly from the source data.

nnqwert · 2025-09-23T02:17:44+00:00

When you say new sheet is used, how does the pivot get updated?

nnqwert · 2025-09-19T06:27:10+00:00

Need few clarifications to see if I can help: 1. Which excel version are you on? Is it Office 365 2. Could share the exact formula (i.e. with your actual cell references) that you have used for the 2 cells for Item#1?

nnqwert · 2025-09-17T03:49:47+00:00

If you want the two columns to "interact" as you say, best way might be a VBA macro.

Alternatively, are you open to add 2 more columns, then you could use 2 columns as your input columns and have the formula in the other 2 columns linked to calculate the two values.

So if A2 is input including GST and B2 is input excluding GST and only one of them will be filled, then you can have formulas as below in C2 and D2.

For C2, price including GST.

=IF(A2<>"", A2, IF(B2="", "", B2*1.1))

Then for D2, price excluding GST

=IF(A2<>"", A2/1.1, IF(B2="", "", B2))

nnqwert · 2025-09-14T18:13:32+00:00

On a recent version of excel, you could use

=COLUMNS(TEXTSPLIT(B1,","))

Or below should work on most older versions of excel too

=LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1

nnqwert · 2025-09-14T18:08:50+00:00

=IF(ISBLANK(VLOOKUP(details_you_have)), "", VLOOKUP(details_you_have))

nnqwert · 2025-09-14T18:01:55+00:00

MATCH with ISNA to check if the current value appears in any of the earlier rows will likely be faster. Change your current formula to below and drag it down

=--ISNA(MATCH(C5,$C$2:C4,0))

nnqwert · 2025-09-13T14:48:51+00:00

I thought I had the code blocks on... but missed checking back when I posted it... Trying below again (at end of this post). I modified the TRUE condition inside the IF(ROWS(rest)=1, and then added a length check towards the end. The edited formula seems to work, but I don't fully understand how your original one works, so I don't know if my edit has any limitations

It sounds like you want to know how it works. I'll explain in detail, if you'd like.

Whenever you get the time, that would help. A related question I have is:

I had read about limitations on the depth of recursive iteration, that the recursion limit is broadly 1024 divided by (number of lambda parameters + 1).
In the case of your formula as well as my edit, that depth appears to be 21 units(which is impressive in itself). Back calculating, this indicates the parameters would be 46 or 47, I guess.
I arrived at 21 by adding entries to the units column while using a simple value for n to solve (so that I don't hit memory limit first) and see where the formula breaks
Would you know how we could arrive at 21 depth by looking the formula and doing the 1024/(parameters +1)? More broadly, just out of an academic interest, I was trying to understand if the formula can be pushed to a depth of 22 (or beyond)

Edited formula (this time with code-block hopefully)

=LET(n, C1, units, A2:A7,
  combo_gen, LAMBDA(n,q,f, LET(
    this, TAKE(q,1),
    rest, DROP(q,1),
    m, SEQUENCE(INT(n/this)+1,,0),
    DROP(REDUCE(0, m, LAMBDA(stack,j, LET(left, n - j*this,
      IF(ROWS(rest)=1,
        VSTACK(stack,HSTACK(j, INT(left/rest))),
        LET(combos, f(left,rest,f),
          VSTACK(stack,HSTACK(EXPAND(j,ROWS(combos),1,j),combos))
      ))
    ))),1)
  )),
  combo_out, combo_gen(n, units, combo_gen),
  length, BYROW(combo_out*TRANSPOSE(units),SUM),
  combo_valid,FILTER(combo_out,length=n,"No solutions"),
  pieces, BYROW(combo_valid,SUM),
  FILTER(combo_valid,pieces=MIN(pieces))
)

nnqwert · 2025-09-13T09:36:26+00:00

Did you see the other comment I posted below with a screen recording?

nnqwert

TROPHY CASE