new to macros

[JerVD]

hey all

I'm completely new to macros in excel. I'm trying to make a button on my sheet that takes the number in one cell (will be formatted as a currency and is being calculated using a formula.) and puts it in the next available cell in a column.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]$100.00[/TD]
[TD][/TD]
[TD]1/12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2/12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3/12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]4/12[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

first press A1 to B1
next press A1 to B2
next press A1 to B3
and so on

I appreciate the help with this
I'm on Excel 2016 Mac

Jer

 

[alansidman]

Attach this code to a button.

Option Explicit


Sub Jer()
    Dim lr As Long
    lr = Range("B" & Rows.Count).End(xlUp).Row
    If Range("B1") = "" Then
        Range("A1").Copy
        Range("B1").PasteSpecial xlPasteValues
    Else
        Range("B" & lr + 1).PasteSpecial xlPasteValues
    End If


End Sub

 

[My Aswer Is This]

I modified previously posted script.

That script only did copy if..

Try this:

Option Explicit
Sub Jer()
'Modified  12/27/2018  8:38:34 PM  EST
    Dim lr As Long
    lr = Range("B" & Rows.Count).End(xlUp).Row
    Range("A1").Copy
    If Range("B1") = "" Then
        Range("B1").PasteSpecial xlPasteValues
    Else
        Range("B" & lr + 1).PasteSpecial xlPasteValues
    End If
Application.CutCopyMode = False
End Sub

 

[JerVD]

hey so I am getting a compile error.
"x1Up" is highlighted.

I did change the cell references to the actual ones I'm using in my sheet.

I ended up with this.


Dim lr As Long
lr = Range("I" & Rows.Count).End(xlUp).Row
Range("J8").Copy
If Range("I25") = "" Then
Range("I25").PasteSpecial xlPasteValues
Else
Range("I" & lr + 1).PasteSpecial xlPasteValues
End If
Application.CutCopyMode = False


J8 is the cell im trying to copy from
and I25 thru I55 is where im trying to paste.

what did I mess up?

 

[Rick Rothstein]

hey so I am getting a compile error.
"x1Up" is highlighted.

The second character is not a one, it is a lower case L.

 

[My Aswer Is This]

Why did you ask for:

first press A1 to B1
next press A1 to B2
next press A1 to B3

But now ask for something completely different?
​

 

[My Aswer Is This]

This script works for me

hey so I am getting a compile error.
"x1Up" is highlighted.

I did change the cell references to the actual ones I'm using in my sheet.

I ended up with this.


Dim lr As Long
lr = Range("I" & Rows.Count).End(xlUp).Row
Range("J8").Copy
If Range("I25") = "" Then
Range("I25").PasteSpecial xlPasteValues
Else
Range("I" & lr + 1).PasteSpecial xlPasteValues
End If
Application.CutCopyMode = False


J8 is the cell im trying to copy from
and I25 thru I55 is where im trying to paste.

what did I mess up?

 

[JerVD]

Why did you ask for:

first press A1 to B1
next press A1 to B2
next press A1 to B3

But now ask for something completely different?
​

Sorry I figured it was easier to show what I wanted on a simple table rather than on what I was actually working on. I always try to decode the things people help me with so I can learn. so I take what people help me with and try to rebuild it for myself in the situation I want it for.