Running a macro in an active cell

[lexi0088]

I know this may seem very simple, but I can not get this to work.

I have a button in excel that is linked to a macro. I want to be able to click on a random cell in column J and then click the button to run a macro in order to insert value into that cell.

My problem is that I cannot figure out how to make that cell in column J that I choose the active cell for my macro. Here is my macro. Any suggestions would be appreciative.

Application.Calculation = xlCalculationManual

ActiveRow = ActiveCell.Row
ActiveColumn = 10
RowNum = Range("J1").Value
Cells(ActiveRow, ActiveColumn).Value = Range("'Expense'!A" & RowNum).Value
Cells(ActiveRow, ActiveColumn + 1).Value = Range("'Expense'!B" & RowNum).Value
If Application.MoveAfterReturnDirection = xlToRight Then
Cells(ActiveRow, ActiveColumn + 2).Select
Else
Cells(ActiveRow + 1, ActiveColumn).Select
End If
Application.Calculation = xlCalculationAutomatic

 

[SkipVought]

lexi0088,

ActiveCell is the active cell

If you are saying that you want the ROW of the active cell in column J, then

Cells(ActiveCell, "J").Value = MyValue

or

Cells(ActiveCell, 10).Value = MyValue

So here's what it seems to me that you want to do

    ActiveRow = ActiveCell.Row
    ActiveColumn = 10
    Cells(ActiveRow, ActiveColumn).Value = Worksheets("Expense").Cells(RowNum, "A").Value

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[lexi0088]

That still is not working

 

[SkipVought]

What IS happening? Any error?

Put a break in your code on

Worksheets("Expense").Cells(RowNum, "A").Value

and tell me what the value of this is using the Watch Window.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[lexi0088]

It is saying can't compile module.

Here is What I have:

Sub Macro1()
ActiveRow = ActiveCell.Row
ActiveColumn = 10
RowNum = Range("J1").Value
Cells(ActiveRow, ActiveColumn).Value = 'Worksheets("Expense").Cells(RowNum, "A").Value
Cells(ActiveRow, ActiveColumn + 1).Value = Worksheets("Expense").Cells(RowNum, "A").Value
End Sub

Please let me know if I am missing something, I have never written something like this before

 

[SkipVought]

loose the tic mark

Sub Macro1()
ActiveRow = ActiveCell.Row
    ActiveColumn = 10
    RowNum = Range("J1").Value
    Cells(ActiveRow, ActiveColumn).Value = Worksheets("Expense").Cells(RowNum, "A").Value
    Cells(ActiveRow, ActiveColumn + 1).Value = Worksheets("Expense").Cells(RowNum, "A").Value
End Sub

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[lexi0088]

I did that and it still does not work. Can you refer me to any other forum ar website that has some type of example like this?

 

[SkipVought]

...it still does not work." Exactly what is happening?

What's in J1?

AGAIN...

What's the VALUE of Worksheets("Expense").Cells(RowNum, "A").Value?




Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[lexi0088]

J1 is a drop down list where a person chooses an expense type. This list is in worksheet "Expense".

In column A of "Expense" is the type of expense
In column B of "Expense" is the code pertaining to the type of expense

In the worksheet that I am working in, each row contains a different person's details, such as name and vendorID. What I am trying to do is be able to go to a paticular person (a row), activate that cell in column J, then go to J1 and choose the correct expense. From here I would press the button which is linked to the macro.

The macro would go to my active cell and in column J, put the value from J1. In column K, it would but the corresponding code from "Expense".

Whn I run the macro it is saying "runtime error '13' type mismatch" and it higlights

Cells(ActiveRow, ActiveColumn).Value = Worksheets("Expense").Cells(RowNum, "A").Value

The value on the watch says "<out of context>"
the type is variant/empty


I would like to thank you for taking the time to help me out. I really appreciate it.

 

[SkipVought]

Then why are you putting

Expense Type into RowNum

and using RowNum in Worksheets("Expense").Cells(RowNum, "A").Value

???

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}