Enter Data Into Cell With VB Code

montgoml

New Member
Joined
Jul 10, 2008
Messages
8
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
not clear what is meant by currentrow1.

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

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Jul 10, 2008
Messages
8
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.
 
Last edited:

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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:

Code:
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:

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,517
Messages
5,832,223
Members
430,116
Latest member
d1pakjha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top