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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

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