Copying of one Cell Value each Row as excel runs down the Page.

[OutOfMyDepth]

G'day,

All that is needed is for Column I (eye) to be a copy of Column F.

HOWEVER,

1) Just using a formula to copy it over creates a Circular Reference.

2) Turning on/off the Iterative Calculation (File/Options/Formula) causes calculation problems down the page.

3) It also MUST be that it excel processes across the Row, then excel processes the first 2 cells of the next Row,

THEN it VBA's the next cell and the excel processes the rest of the Row etc.

I hope I've explained 3) adequately.

Glenn.

[Leith Ross]

Hello Glenn,

Sometimes it just takes getting away from what you are doing to see the answer. You have duplicated the formula in columns "F" and "I". To get the result from column "I" to column "F" all you need to do is use the formula =I6 in cell F6.

I have made the changes to the workbook including resetting the calculation options.

[OutOfMyDepth]

Hello Glenn,

Sometimes it just takes getting away from what you are doing to see the answer. You have duplicated the formula in columns "F" and "I". To get the result from column "I" to column "F" all you need to do is use the formula =I6 in cell F6.

I have made the changes to the workbook including resetting the calculation options.

Thank you Leith for replying.

I now see where my explanation in 1) was inadequate: it should of been more like:
1) Just using a formula to copy it over creates a Circular Reference down the page,
the arrays in the formulas in F & I are slightly different,
Column I's formulas are only there or the page wouldn't have any Data,
BUT the VALUE that needs to be there is what is in Column F,
AND it must be worked out Row by Row as the excel page is 'FORMED'.

Thanks again Leith for replying, I hope I've done a bit better explaining myself now.

Kind Regards,
Glenn.

[OutOfMyDepth]

G'day,

As I'm not proficient in even excel, let alone VBA,
I was wondering if the following made any sense,

Public Function CopyFromFintoI()
 Dim lRow, x As Integer
lRow = Range("I" & Rows.Count).End(xlUp).Row
x = 5
Do
    x = x + 1
    Range("I" & x).Value = Range("F" & x).Value
   Loop Until IsEmpty(Range("F" & x + 1)) Or x = 100

Does anyone know if this is the right track,
how do I intermingle this idea with excel still running on each row?

Kind Regards,
Glenn.

[jolivanes]

Maybe just explain what you want to accomplish.

[OutOfMyDepth]

G'day jolivanes,

I don't know what else to write,
I've explained what I want to accomplish.

Kind Regards,
Glenn.

[Paul_Hossler]

I now see where my explanation in 1) was inadequate: it should of been more like:
1) Just using a formula to copy it over creates a Circular Reference down the page,
the arrays in the formulas in F & I are slightly different,
Column I's formulas are only there or the page wouldn't have any Data,
BUT the VALUE that needs to be there is what is in Column F,
AND it must be worked out Row by Row as the excel page is 'FORMED'.

Thanks again Leith for replying, I hope I've done a bit better explaining myself now.

Kind Regards,
Glenn.

I'm afraid I don't understand the relationships in the data either

What do you mean 'using a formula to copy it over?

What do you mean about col I values are only there or the page wouldn't have any data?

If the arrays in formulas in F & I are different, why would the VALUE in Col have what is in col F?

What does "AND it must be worked out Row by Row as the excel page is 'FORMED'" mean?



I did notice that some of your formulas were not using absolute addressing ( the $'s) and a copy/fill down will make relative adjustments. I don't think that you wanted it?

Other formulas use data up to the row before, but that might be intentional

Col F is

=IF(G6="Left",VLOOKUP(H6,BF$4:BG15,2,FALSE),VLOOKUP(H6,BI$4:BJ15,2,FALSE))
=IF(G7="Left",VLOOKUP(H7,BF$4:BG16,2,FALSE),VLOOKUP(H7,BI$4:BJ16,2,FALSE))
=IF(G8="Left",VLOOKUP(H8,BF$4:BG17,2,FALSE),VLOOKUP(H8,BI$4:BJ17,2,FALSE))
=IF(G9="Left",VLOOKUP(H9,BF$4:BG18,2,FALSE),VLOOKUP(H9,BI$4:BJ18,2,FALSE))
=IF(G10="Left",VLOOKUP(H10,BF$4:BG19,2,FALSE),VLOOKUP(H10,BI$4:BJ19,2,FALSE))
=IF(G11="Left",VLOOKUP(H11,BF$4:BG20,2,FALSE),VLOOKUP(H11,BI$4:BJ20,2,FALSE))
=IF(G12="Left",VLOOKUP(H12,BF$4:BG21,2,FALSE),VLOOKUP(H12,BI$4:BJ21,2,FALSE))
=IF(G13="Left",VLOOKUP(H13,BF$4:BG22,2,FALSE),VLOOKUP(H13,BI$4:BJ22,2,FALSE))
=IF(G14="Left",VLOOKUP(H14,BF$4:BG23,2,FALSE),VLOOKUP(H14,BI$4:BJ23,2,FALSE))
=IF(G15="Left",VLOOKUP(H15,BF$4:BG24,2,FALSE),VLOOKUP(H15,BI$4:BJ24,2,FALSE))

but I think it should be

=IF($G6="Left",VLOOKUP($H6,BF$4:$BG$15,2,FALSE),VLOOKUP($H6,BI$4:$BJ$15,2,FALSE))
=IF($G7="Left",VLOOKUP($H7,BF$4:$BG$15,2,FALSE),VLOOKUP($H7,BI$4:$BJ$15,2,FALSE))
=IF($G8="Left",VLOOKUP($H8,BF$4:$BG$15,2,FALSE),VLOOKUP($H8,BI$4:$BJ$15,2,FALSE))
=IF($G9="Left",VLOOKUP($H9,BF$4:$BG$15,2,FALSE),VLOOKUP($H9,BI$4:$BJ$15,2,FALSE))
=IF($G10="Left",VLOOKUP($H10,BF$4:$BG$15,2,FALSE),VLOOKUP($H10,BI$4:$BJ$15,2,FALSE))
=IF($G11="Left",VLOOKUP($H11,BF$4:$BG$15,2,FALSE),VLOOKUP($H11,BI$4:$BJ$15,2,FALSE))
=IF($G12="Left",VLOOKUP($H12,BF$4:$BG$15,2,FALSE),VLOOKUP($H12,BI$4:$BJ$15,2,FALSE))
=IF($G13="Left",VLOOKUP($H13,BF$4:$BG$15,2,FALSE),VLOOKUP($H13,BI$4:$BJ$15,2,FALSE))
=IF($G14="Left",VLOOKUP($H14,BF$4:$BG$15,2,FALSE),VLOOKUP($H14,BI$4:$BJ$15,2,FALSE))
=IF($G15="Left",VLOOKUP($H15,BF$4:$BG$15,2,FALSE),VLOOKUP($H15,BI$4:$BJ$15,2,FALSE))

------------------------------------------------
Col I is

=IF(G6="Left",VLOOKUP(H6,AU$4:AV15,2,FALSE),VLOOKUP(H6,AZ$4:BA15,2,FALSE))
=IF(G7="Left",VLOOKUP(H7,AU$4:AV16,2,FALSE),VLOOKUP(H7,AZ$4:BA16,2,FALSE))
=IF(G8="Left",VLOOKUP(H8,AU$4:AV17,2,FALSE),VLOOKUP(H8,AZ$4:BA17,2,FALSE))
=IF(G9="Left",VLOOKUP(H9,AU$4:AV18,2,FALSE),VLOOKUP(H9,AZ$4:BA18,2,FALSE))
=IF(G10="Left",VLOOKUP(H10,AU$4:AV19,2,FALSE),VLOOKUP(H10,AZ$4:BA19,2,FALSE))
=IF(G11="Left",VLOOKUP(H11,AU$4:AV20,2,FALSE),VLOOKUP(H11,AZ$4:BA20,2,FALSE))
=IF(G12="Left",VLOOKUP(H12,AU$4:AV21,2,FALSE),VLOOKUP(H12,AZ$4:BA21,2,FALSE))
=IF(G13="Left",VLOOKUP(H13,AU$4:AV22,2,FALSE),VLOOKUP(H13,AZ$4:BA22,2,FALSE))
=IF(G14="Left",VLOOKUP(H14,AU$4:AV23,2,FALSE),VLOOKUP(H14,AZ$4:BA23,2,FALSE))
=IF(G15="Left",VLOOKUP(H15,AU$4:AV24,2,FALSE),VLOOKUP(H15,AZ$4:BA24,2,FALSE))

but I think it should be

=IF($G6="Left",VLOOKUP($H6,$AU$4:$AV$15,2,FALSE),VLOOKUP($H6,$AZ$4:$BA$15,2,FALSE))
=IF($G7="Left",VLOOKUP($H7,$AU$4:$AV$15,2,FALSE),VLOOKUP($H7,$AZ$4:$BA$15,2,FALSE))
=IF($G8="Left",VLOOKUP($H8,$AU$4:$AV$15,2,FALSE),VLOOKUP($H8,$AZ$4:$BA$15,2,FALSE))
=IF($G9="Left",VLOOKUP($H9,$AU$4:$AV$15,2,FALSE),VLOOKUP($H9,$AZ$4:$BA$15,2,FALSE))
=IF($G10="Left",VLOOKUP($H10,$AU$4:$AV$15,2,FALSE),VLOOKUP($H10,$AZ$4:$BA$15,2,FALSE))
=IF($G11="Left",VLOOKUP($H11,$AU$4:$AV$15,2,FALSE),VLOOKUP($H11,$AZ$4:$BA$15,2,FALSE))
=IF($G12="Left",VLOOKUP($H12,$AU$4:$AV$15,2,FALSE),VLOOKUP($H12,$AZ$4:$BA$15,2,FALSE))
=IF($G13="Left",VLOOKUP($H13,$AU$4:$AV$15,2,FALSE),VLOOKUP($H13,$AZ$4:$BA$15,2,FALSE))
=IF($G14="Left",VLOOKUP($H14,$AU$4:$AV$15,2,FALSE),VLOOKUP($H14,$AZ$4:$BA$15,2,FALSE))
=IF($G15="Left",VLOOKUP($H15,$AU$4:$AV$15,2,FALSE),VLOOKUP($H15,$AZ$4:$BA$15,2,FALSE))

Anyway, the changes above are in the attachment, and I don't think there is any circular references

[Roty1967]

Hi everyone,

On a similar note, I'm trying to solve a problem similar to Glenn's so I'm going to combine my question with what I think Glenn wants to see if we can kill two birds with the same macro :-)

I'm a Visual Basic programmer and pretty useful in Excel but new to VBA and it's syntax so looking for some help with the correct commands / syntax for VBA to do the following -

I want to create my own custom function which will execute just like a standard Excel function would.

Here is the psuedo code for what I want to do (to keep it simple I'm trying to get it to do what I think Glenn is looking for)

function CopyFtoI()


(currentSheet.cell.I & currentRowNumber) = (currentSheet.cell.F & currentRowNumber)

end function

I want a function that would return the value of Column F on the same row as Column I. So if the function was run on row 25 it would return the value of F25 into I25. If the function was run on row 99 it would return the value of F99 and return it into I99 etc

I believe I would call the function by typing the following into any cell in my sheet

=copyFtoI()


Can someone please recode my request in VBA please? I wasn't sure whether I needed to pass any arguments to the function to make it work such as the currentRow number? If so, please amend my code so that it does what I want, which is to look at the row on which the function is called and then copy the contents of F into I.

This would help me out and I would also I think provide Glenn with a possible solution to his problem if a simple =Fx statement won't work.

Many thanks, let me know if you have questions or my explanation isn't clear.

Regards
Roty1967 (Andy)

[Paul_Hossler]

I want a function that would return the value of Column F on the same row as Column I.
So if the function was run on row 25 it would return the value of F25 into I25.
If the function was run on row 99 it would return the value of F99 and return it into I99 etc

Asking again ... why can you not just put '=F25' in I25?

[OutOfMyDepth]

G'day Paul,

In my case what I posted as an example was a cut down simplifies version.

The 'real' spreadsheet is half as many MORE columns and 200,000 rows and further down the page columns F & I (which have different arrays) end up becoming a CR. If I turn OFF the thingamajig that calculates (File/Options/Formulas, etc.) it gives the wrong answer DOWN THE PAGE.

I checked posts before I PMed you and I found where you had 'turned off' the excel calculations while the vba had ran.

What I gather I need is for the excel to start running but each time it gets to the I (eye) column it uses VBA to put in the VALUE of the F column (SAME ROW) and then go back to exceling until the next row's I column.

It is NO use it exceling the WHOLE page and then Changing I column BECAUSE the 'CURRENT' row is dependent on what is in F (and I) columns.

I'm sorry I'm no clearer in my explanation.

Thanks for all your help.
Glenn.

[Paul_Hossler]

Maybe this?

It goes in the Sheet1 code page (see attachment) and every time the sheet is calculated, any I cells that have a formula or an error will be replaced by the F cell value (no formula)

Capture.JPG

Option Explicit

Private Sub Worksheet_Calculate()
    Dim i As Long
    Dim rCellinF As Range, rCellinI As Range
    
    Application.EnableEvents = False
    
    Me.Calculate
    
    Application.Calculation = xlCalculationManual
    
    For Each rCellinF In Range(Range("F6"), Range("F6").End(xlDown)).Cells
        '   Me = this worksheet
        Set rCellinI = Me.Cells(rCellinF.Row, 9)
        If rCellinI.HasFormula Or Application.WorksheetFunction.IsError(rCellinI) Then
            rCellinF.Copy
            rCellinI.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Me.Calculate
        End If
    Next
    
    Application.Calculation = xlCalculationAutomatic
   
    Application.EnableEvents = True
End Sub

[OutOfMyDepth]

G'day Paul,

THANK YOU,

Between the usual family weekend activities AND my excel / VBA inabilities I'm slowly getting it happening.

On my full size REAL excel page it is now in place but I can't get it to Run.

I will continue to work on it AND Thanks again for all your patient help.

Kind Regards,
Glenn.

[Paul_Hossler]

If you put the macro on the worksheet's code page (like screen shot and attachment) it should run every time the worksheet recalculates

Add the marked line temporarily

    Application.Calculation = xlCalculationManual
    
    MsgBox "Recalculating"  '   ----------------------------------

1. Change G5 from 'Right' to 'Left'

ALso

2. Put 123 in F6 and =1+1 in I6

The 123 is just a marker, and the formula is because only Col I formula cells are updated with the value in Col F