• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

include formula to a last filled column range

rrocker1405

rrocker1405

Member
Hi,

what is the correct way to include a formula to a filled column range for example

Column A is filled from A1: a38 and I want my formula to included in column A39 what should be the syntax?

I'm using the following formula to identify the last row lastrow

ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

Please advise...

Thanks in advance.
 
Hi Narayan,

thank you for the link. I modified the code to the below. I'm able to navigate to the last filled cell range but not sure how to paste the formula after the last filled cell range. could you please advise.

many thanks once again.

Sub workingtest()
i = 2

For Each c In Range("c1:az19")
c.FormulaArray = "=IFERROR(INDEX(Trace[Child ID],SMALL(IF((RC1=Trace[Parent ID])*(COUNTIF(RC2:RC,Trace[Child ID])=0),ROW(Trace[Parent ID])-MIN(ROW(Trace[Parent ID]))+1,""""),1)),"""")"
i = i + 1
Next
Dim j As Long, r As Range, lastrw As Long
j = InputBox("type the number of rows to be insered")
lastrw = Cells(Rows.Count, "A").End(xlUp).Row
Set r = Range("A1")
Do
Range(r.Offset(1, 0), r.Offset(j, 0)).EntireRow.Insert
Set r = Cells(r.Row + j + 1, 1)
If r.Offset(1, 0) = "" Then Exit Do
Loop
End Sub
 
Hi Anand ,

I am sorry , but nothing is clear to me. I do not know what you are doing inserting rows based on user input , why you are looking for the last row , or which formula you wish to insert.

Probably if you can upload a file , it will be clear. You can wait for someone else to answer.

Narayan
 
Attached is the file that im working on presently.

the macro
a. inserts the formula for me in my specified range
b. inserts blank rows for me
c. updates formula for me but it does only until one row above the filled range

d. after this is sorted I have to repeat the same steps to include another set of formulas.

This is what I'm trying to achieve Narayan.

I'm sorry but am stuck with this for quite sometime and its because of my very minimal knowledge on vba.

thanks in advance.

kind regards,
A!
 

Attachments

  • Loop.xlsm
    326.3 KB · Views: 1
works perfect Narayan.

you had stated in ur last post that don't use used range. can i keep using this as sometimes calculates for other ranges than specified.

please advise.

Kind regards,
Anand
 
Hi Anand ,

I was specifically talking of using the UsedRange property in finding out the last cell with data in a particular column.

UsedRange can be used in other situations , though even there other alternatives can be considered , because the UsedRange property is not a reliable indicator of cells with data.

Narayan
 
thank you for the detailed explanation narayan.

this code that I use now generates the desired results but sometimes the last row range is taken beyond the filled in range. for example the values are filled only till row 38 but the formulas are updated for 73 rows which i dont want and this happens if i use the macro for the 2nd time and if i delete the complete content and try it works perfectly.

please advise.

kind regards,
Anand
 
Hi Anand ,

The problem is because of the detection of the last row , which when you run the macro is based on data prevailing then ; once you run the macro , since data is added , the calculation of lastrw will get affected.

Nothing much can be done , unless you can put a value in a cell when you run the macro ; when you run the macro , first check to see if this cell has a particular value ; if it doesn't then the macro can be run , but if the cell already has that value , it means the macro has been run , and need not be run again.

Narayan
 
thank you for explaining me this.

im very sure that I will soon enroll myself for the VBA and advanced excel class in chandoo.org to be more proficient in the usage of excel

many thanks narayan.

Kind regards,
A!
 
You must log in or register to reply here.
Back
Top