Share via

VBA to use offset to enter data in a range instead of a particular cell.

Anonymous
2021-07-23T13:36:36+00:00

Hi,

Not that great in VBA but still managed to write a code that:

  1. Check if a existing cell is empty.
  2. If empty, place 1 in the cell left to it.

Sub markAsPaid()

'this macro will print 1 in the Left cell beside the selected cell

If ActiveCell.Value <> "" Then

ActiveCell.Offset(0, -1).Value = 1

End If

End Sub

The macro works great but the problem is it becomes too time consuming to keep doing it one cell at a time.

Is there any way where I could print 1 in entire selected range in one shot rather than one at a time ??

Any help is appreciated.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-07-23T17:42:52+00:00

    Re: "instead of marking cells to the left"

    I was reading...
    "where I could print 1 in entire selected range in one shot".

    NLtL

    0 comments
  2. Anonymous
    2021-07-23T16:11:00+00:00

    Hey,

    thanks for the help. But this code marks the selected range as 1 instead of marking the cells to its left.

    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2021-07-23T13:48:01+00:00

    Try this:

    Sub markAsPaid()
    With Selection.Offset(0, -1)
    .FormulaR1C1 = "=IF(RC[1]="""","""",1)"
    .Value = .Value
    End With
    End Sub

    0 comments
  4. Anonymous
    2021-07-23T13:46:56+00:00

    re: fill a range with a value

    '---

    Sub MarkAsPaidR1()
    Dim rng As Excel.Range

    Set rng = Excel.Selection
    rng.Value = 1
    End Sub
    '---

    NLtL

    https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU (free excel programs)

    0 comments