Fill up one cell

[Derek McGill]

I am filling Col a and e with formula's with the following code, How do i change it to only fill up only one cell

Private Sub CommandButton1_Click()
Dim GradingCol As Integer
Dim LastRow As Long

If Len(TextBox2.Value) = 0 Then
MsgBox "The Name field can not be left empty!", vbOKOnly, "Derry Gradings System"
TextBox2.SetFocus
Exit Sub
End If

If TextBox4.Value = "" Then Call CommandButton2_Click
Sheets("Gradings").Select

GradingCol = 4

'Find the last used row in a Column: column A in this example
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).row
        LastRow = LastRow + 1
    End With

'Activate Bottom Cell
Cells(LastRow, GradingCol).Activate

'Collect all Inputs and place in (Grading) Sheet
    ActiveCell.Value = TextBox4.Value
    ActiveCell.Offset(0, -1) = TextBox3.Value
    ActiveCell.Offset(0, -2) = TextBox2.Value
         
' Add formulas to col A and E
 LastRow = Range("B65536").End(xlUp).row 'Find the row number of the last cell used in column "B"
    Range("A2").Resize(LastRow - 1).Formula = "=B2 &"" ""& C2"
 LastRow = Range("D65536").End(xlUp).row 'Find the row number of the last cell used in column "D"
    Range("E2").Resize(LastRow - 1).Formula = "=IF(D2<1200,40,IF(D2<1500,32,IF(D2<1800,24,IF(D2<2100,16,8))))"
Call SortLookupTable
Unload Me
End Sub

 

[NARAYANK991]

Hi ,

The following two lines of code are entering the formula in the entire columns :

Range("A2").Resize(LastRow - 1).Formula = "=B2 &"" ""& C2"

Range("E2").Resize(LastRow - 1).Formula = "=IF(D2<1200,40,IF(D2<1500,32,IF(D2<1800,24,IF(D2<2100,16,8))))"

If you omit the portions highlighted in red , then only the cells A2 and E2 will have the formula entered in them.

Narayan

 

[Derek McGill]

Hi Narayan
My code puts a new row of data into a table which has around 350 rows of data the problem is to add the formula on each end in col a and e

 

[Monty]

Derek McGill

Same data will help to help you.

 

[Derek McGill]

Here is the sheet.

 

[Monty]

sorry may be iam understanding can you correct me..What do you meen by..

How do i change it to only fill up only one cell?

 

[Derek McGill]

How do I copy down the formula in cells A350 and E350 to A351 and E351 after I input data from a UserForm in to Col B to D ?

 

[Monty]

Interesting Question...i Can't see any userform Mr Derk to help you..

 

[Derek McGill]

The userform code is in the first post.

 

[Monty]

Am not looking for code...wanted a userform in the excel for which i can write code for you.

 

[Derek McGill]

I have added the code and userform to file

 

[Monty]

Hello Derek.

Your userform is perfectly working as per my testing..No changes required.

When i have entered "Name","Surname" & "Grading" its perfectly taking data and formula in "A" column and "E" column.

Let me know if am wrong.

 

[Derek McGill]

Its working, I just want to learn how to ONLY fill ONE formula on each side of the new data rather than refilling up all of the columns each time from A2 and E2.

 

[NARAYANK991]

Hi Derek ,

See if this works.

Narayan

 

[Derek McGill]

Narayan
Thanks it works.

Range("A1").Offset(LastRow - 1).Formula = "=B" & LastRow & " & " & """ """ & " & " & "C" & LastRow
This is what i wanted rather than the

Range("A2").Resize(LastRow - 1).Formula = "=B2 &"" ""& C2"
That I was using.