is there any way to have a variable range in a row for up to the cell the formula is typed in - can be VBA

[Dave01]

Hi,

thanks for anybodies help on this one,

so I have this formula which appears in column AK,

=IF(COUNTIF(A2:AJ2,"*"&lookups!$G$5&"*"),lookups!$H$5," ")

but what I am having a problem with is the A2:AJ2 - the range, what I have come to realise is this may change based on which column the formula is written in.

Example

I download some data, and my end row is column AJ so the formula gets written in AK. (VBA finds the last used row in the last column moves to the next column adds the formula in AJ - works perfect)

then the next day I download the data again, and find two more columns have been added, so my range now needs to be A2:AL2, the formula would be written in AM2

in other words the range can move from A2 to the cell before the cell where the formula is written,

and work with this formula

=IF(COUNTIF(A2:AJ2,"*"&lookups!$G$5&"*"),lookups!$H$5," ")


for info

lookups!$G$5 = '@A
lookups!$H$5 = "Fail"


so it would be something like =IF(COUNTIF(A2: last used cell before the formula, and then all the rest of the formula.

thanks for any help

David

 

[Micron]

Not clear how the formula is being written. If you're typing it into the cells, you need to use vba to first determine the last column with data then enter the formula via code in the correct column with the correct range reference? I don't have any code at hand that does this, but it seems to me that it might be the way to go.

I have also created dynamic ranges (based on rows, not columns) and used the named range when copying data from Access to Excel, so maybe that's an option for you. However, I cannot say if a formula can use a named range instead of A2:J2. I'm not real strong in Excel formulas. If you go that route, you also need to determine where the range has to be created - workbook or sheet level. However, a dynamic range might pick up the formula which you would not want and I'm guessing that you can't put it on another sheet to avoid that. An example of a dynamic range:
=OFFSET(DATA!$B$6,0,0,COUNTA(DATA!$B:$B),1)

HTH.

 

[Dave01]

Hi,

thanks for the reply, I thought someone would come back and say it wasnt clear , withought providing something working

this is a working copy of the macro

Payment Fail Template.xlsm


this is what makes it find the end of each row/column and enter what it needs to do, the formula Im stuck on isnt in there as I havnt put it in.

- if you look at the back up tab, youll see how it all is supposed to work, the issue is the columns may or may not change, so thats why I need the macro to adjust the ranges

'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'count rows from Criterion 1 on Master Sheet to the end of rows, and enter Criterion 1 -> x
'Apply formula in in Q2 to only include first 3 characters
'Copy this to end of column, and fill down to end of Rows
'Future proofed if extra criterions are added
'Not if Layouts change then Q1 & Q2 need to change to in coding
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


Dim GetLastColumn As Long, GetLastRow As Long
GetLastColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
GetLastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Range("Q1").Activate
ActiveCell.FormulaR1C1 = "='Master Sheet'!RC"
Range(ActiveCell, Cells(ActiveCell.Row, GetLastColumn)).FillRight

Range("Q2").Activate
ActiveCell.FormulaR1C1 = "=LEFT('Master Sheet'!RC,3)"
Range(ActiveCell, Cells(ActiveCell.Row, GetLastColumn)).FillRight
Range(ActiveCell, Cells(GetLastRow, GetLastColumn)).FillDown



'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Search for column headers used in formulas and name columns
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Sheets("Summary Sheet").Range("A1").Activate
Cells.Find(What:="Partner name 1 and 2", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
Range(Selection, Selection.End(xlDown)).Name = "CompanyName"

Sheets("Summary Sheet").Range("A1").Activate
Cells.Find(What:="Payment Currency", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
Range(Selection, Selection.End(xlDown)).Name = "PaymentCurrency"


'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'Apply formulas to end of rows
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Cells(1, GetLastColumn + 1).FormulaR1C1 = "Vendor List Match"
Cells(2, GetLastColumn + 1).FormulaR1C1 = "=IFERROR(IF(MATCH(CompanyName,lookups!C[-34],0),""MATCH"",""NO MATCH""),""NO MATCH"")"

Cells(1, GetLastColumn + 2).FormulaR1C1 = "Vendor Currency Match"
Cells(2, GetLastColumn + 2).FormulaR1C1 = "=IFERROR(IF(AND(MATCH(PaymentCurrency,lookups!C[-35],0),MATCH(RC[-30],lookups!C[-34],0)),""MATCH"",),""NO MATCH"")"

Cells(1, GetLastColumn + 3).FormulaR1C1 = "=""Criterion""&"" ""&CriterionEntered"
Cells(1, GetLastColumn + 4).FormulaR1C1 = "=""Criterion""&"" ""&CriterionEntered"


thanks for your help

 

[Dave01]

- column AM is going to be scrapped as I was just testing stuff, and they dont want it like that.

 

[Micron]

The process isn't exactly clear still. If you download data into a new sheet, your GetLastColumn = Cells.Find(What:="*", should return the rightmost column with data. If you're downloading into the same sheet, then that won't work - your last column contains values that your code inserted on the previous run. You'd have to download into a new sheet and work from there? If you can't work from a new sheet that holds the download data (raw data sheet) then I'm not sure what else to suggest. From what I can see, GetLastColumn needs to refer to a new sheet, not an existing one - unless the download over-writes and there will always be a value in the rightmost column that you can look for instead of "*".