1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Excel VBA, how to enter data into an adjacent cell.

Discussion in 'Business Applications' started by sjtrupp, Jul 26, 2017.

Thread Status:
Not open for further replies.
  1. sjtrupp

    sjtrupp Thread Starter

    Joined:
    Nov 18, 2015
    Messages:
    40
    First Name:
    Steven
    I'm trying to automate a procedure in excel. Basically, I need to look at Column B and if a certain number appears, I need to add information in Column A and Column C.

    So,

    | A | B | C |
    -------------------------
    1 | 9 |
    2 | 7 |

    Would become,

    | A | B | C |
    -------------------------
    1 X | 9 | Y
    2 | 7 |

    I tried to create a macro using the Find button, but it would then hard code in my left arrow movement. I need to look through each cell in Column B, and if 9 appears, add X to column A and Y to Column C for that Row.
    It's a monthly file and the data changes each month. I've got a macro that will add the header information, change the formats of the cells and such, but I can't figure out how to go to a particular cell, and then move over to the left add information, then move over the right two places and add information, and then continue on again. Can anyone assist?
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    There are a couple of methods one is using the Offset property, but the most useful is to use the
    Cells(row,Column) function, because both rows and columns can be manipulated.
    This piece of code removes duplicate rows.


    Dim lastrow As Long, count As Integer

    lastrow = Cells(Rows.count, "A").End(xlUp).Row

    For count = lastrow To 2 Step -1

    If Cells(count, 1) = Cells(count - 1, 1) Then

    Cells(count, 1).EntireRow.Delete xlShiftUp

    lastrow = lastrow - 1

    End If

    Next

    But you would probably want to go down the column, so something like this

    For count = 2 to lastrow ' assumes a Headings Row
    if cells(count, 2) = 9 then
    cells( count, 1) = "X"
    cells(count, 1) = "Y"
    end if
    next count
     
    Last edited: Jul 26, 2017
  3. sjtrupp

    sjtrupp Thread Starter

    Joined:
    Nov 18, 2015
    Messages:
    40
    First Name:
    Steven
    It shouldn't be that easy. I have a book for VBA Access, but there's a lot of differences for VBA Excel.

    Thanks OB. This will make things a lot easier each month.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1193748

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice