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

reddale · Feb 16th 2013

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,"""")

Thanks in advance.

[COLOR="#0000CD"]MODERATOR EDIT

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

cytop · Feb 16th 2013

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...?

patel · Feb 16th 2013

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

Code

arow=activecell.row
fcell=cells(arow,"A")

reddale · Feb 16th 2013

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

Code

Sub find_stock_number_for_disposal()
Dim FindString As String
    Dim Rng As Range
    FindString = Sheets("new disposal entry").Range("b40").Value
    If Trim(FindString) <> "" Then
        With Sheets("stock register list").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
                        End If
        End With
    End If
  
    
    Selection.Offset(0, 13).Select
    
ActiveCell.FormulaR1C1 = _
        "=IF([" &  & "]='new disposal entry'!R40C2,'new disposal entry'!R43C2,"""")"
End Sub

Display More

Clear as mud now, I'll bet.

patel · Feb 16th 2013

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

cytop · Feb 16th 2013

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,"""")"

reddale · Feb 16th 2013

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

Code

Selection.Offset(0, 13).Select
    
    ActiveCell.Value = Sheets("new disposal entry").Range("B43")
    Selection.Offset(0, 1).Select
    ActiveCell.Value = Sheets("new disposal entry").Range("B41")
    Selection.Offset(0, 1).Select
    ActiveCell.Value = Sheets("new disposal entry").Range("B47")
    Selection.Offset(0, 1).Select
    ActiveCell.Value = Sheets("new disposal entry").Range("B49")
    Selection.Offset(0, 2).Select
    ActiveCell.Value = Sheets("new disposal entry").Range("B46")
    Selection.Offset(0, 1).Select
    ActiveCell.Value = Sheets("new disposal entry").Range("B44")
    Selection.Offset(0, 1).Select
    ActiveCell.Value = Sheets("new disposal entry").Range("B55")
    Selection.Offset(0, 1).Select
    ActiveCell.Value = Sheets("new disposal entry").Range("B56")
    Selection.Offset(0, 1).Select
    ActiveCell.Value = Sheets("new disposal entry").Range("B57")
    Selection.Offset(0, 1).Select
    ActiveCell.Value = Sheets("new disposal entry").Range("B58")
    Selection.Offset(0, 1).Select
    ActiveCell.Value = Sheets("new disposal entry").Range("b59")

Display More

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")

I've added the rest since.

Than you for your patience and the shove I needed to work it out.

Cheers

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

Participate now!

Share

Users Viewing This Thread