Enter Data Into Cell With VB Code

[montgoml]

I want VBA to enter a string of text into a cell with a changing row value for one example is this:

Sheets("Data Entry").Range("B" & CurrentRow1).Value = "=TEXT(RIGHT('Data Entry'!Q" & CurrentRow2 & ",8),8)"

Please tell me what needs to be correct. I want to be able to change text in a cell

Thank You In Advance

 

[venkat1926]

not clear what is meant by currentrow1.

try this macro

Sub test()
With Worksheets("dataentry")
'MsgBox .Range("B" & 1).Address
.Range("B" & 1) = Right(.Range("q2"), 8)
End With
End Sub

 

[Smitty]

Welcome to the Board!

Where do you want "CurrentRow" to be? I.E. how do we know how to tell Excel where it is?

If it's the active row, then you can replace CurrentRow with ActiveCell.Row.

Otherwise you'll need to clarify.

Hope that helps,

 

[montgoml]

The currentrow1 is the current row of Column B will go, so if the variable, currentrow1, is 2 then it will go into B2 of the sheet Data Entry (Actually should be refered to as sheet Data Analysis). then the currentrow2 is the row of the other sheet's column Q that is referred to in the equation to be entered into cell. so if currentrow2 is 2 then the cell would be Q2 of the sheet Data Entry. Hopefully this helps clarify somewhat. Also i want to be able to no matter what the equation be able to put an variable in the equation or the cell the text string is put in. I thought i had a grasp on it but i guess not.

 

[montgoml]

Thank you for the Welcome!!!!

 

[Smitty]

So how do we tell Excel that current row is 2? Is it the last used cell?

If so you can use this to set it:

Dim CurrentRow1 as Long
 CurrentRow1 = Sheets("Data Analysis").Cells(Rows.Count,"B").End(xlup).Row

If it needs to be the first empty cell you just need to offset it:

Dim CurrentRow1 as Long
 CurrentRow1 = Sheets("Data Analysis").Cells(Rows.Count,"B").End(xlup).Offset(1).Row