# Use the address of the first cell in the same row as the active cell

• Hello

My goal is to use the cell address of the first cell in the same row that the active cell is in, as the cell address for the "logical test" part of an IF formula.

I've tried to make myself clearer, but it justs ends up looking more confusing.

I think it would be something like

=IF("first cell in current row"='new disposal entry'!R40C2,'new disposal entry'!R43C2,"""")

[COLOR="#0000CD"]MODERATOR EDIT

Please do not use code tags with formulas - - they are reserved for use only with VBA code.[/COLOR]

• Re: Use the address of the first cell in the same row as the active cell

As this is a formula in a particular cell, surely you can simplly refer to column A...
{Assuming formula is in row 5)
[f]
=IF(\$A5="Something", "Something else", "")
[/F]

Our have I completely misunderstood...?

• Re: Use the address of the first cell in the same row as the active cell

Code
``````arow=activecell.row
fcell=cells(arow,"A")``````
• Re: Use the address of the first cell in the same row as the active cell

cytop, because of my limited knowledge of vba, I confuse myself, so it's little wonder you are confused. I have the formula in a macro that finds the cell I want it to find. Then I want the formula to be placed in that cell (which I have succeeded with). where it gets tricky (and stops working), the "logical test" cell address will be different every time (the first cell in the active row is a constant, but the row will be different each time.

This code works as I want it to, except for the logical test in the IF formula. It has to be the first cell in the active cell's row

Clear as mud now, I'll bet.

• Re: Use the address of the first cell in the same row as the active cell

what do you mean for Active cell ?
after find operation it is cell with content = findstring
after "Selection.Offset(0, 13).Select" changes column

• Re: Use the address of the first cell in the same row as the active cell

Quote

...Clear as mud now, I'll bet

I was thinking along the lines of Pitch, Tar or Black crude oil...

Lets's try this:

• You go look for something in the Stock Register List
• If found, you set the cursor to that cell (If not found, there's an issue - your code continues as if it was found)
• You offset 13 columns to the right
• You want to enter a formula in that cell that refers to Col A of whatever row the cursor is on. (Q: Why bother with a formula, why not just enter the relevant value directly in the cell?)

Does that describe what you want...?

Other information - why are the cell references in the following lines hard-coded?

Code
``````FindString = Sheets("new disposal entry").Range("b40").Value
'// and
ActiveCell.FormulaR1C1 = "=IF([" &  & "]='new disposal entry'!R40C2,'new disposal entry'!R43C2,"""")"``````
• Re: Use the address of the first cell in the same row as the active cell

cytop

Thank you for persevering.

The cell references are hard coded because the information I want will be in that cell everytime the macro runs, it will be different data everytime, but in the same cell.

The data will always be found, it has to be entered everytime.

The code works exactly like I want it to, right up to point where I want to use the formula.

After 40 minutes of trying to write a response that made sense, I had a brain burp and came up with a way to do it that didn't involve a formula.

I used

It does what I want it to do, it's a bit slowish, but it does it.
The original code Iposted with the IF formula only went as far as

Code
``ActiveCell.Value = Sheets("new disposal entry").Range("B43")``