Results 1 to 6 of 6

Thread: excel macro - reference to multipple cells

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    9

    excel macro - reference to multipple cells

    I wish to write a macro that will insert static date into column A cell if the column A cell is still empty and name is typed into corresponding column B cell.

    Examble: When I start writing name Michael to the B10 cell macro inserts current date to A10 cell and so on for all A & B cells.

    I tried to use:

    For i = 1 To 1000
    If Worksheets("List1").cell("1,i") = Empty And Worksheets("List1").cell("2,i") <> 0 Then Worksheets("List1").cell("1,i") = Date
    Next i

    and

    If Worksheets("List1").Range("a1:a1000) = Empty And Worksheets("List1").Range("b1:b1000") <> 0 Then Worksheets("List1").Range("a1:a1000") = Date & " " & Time
    Last edited by janko; Feb 24th, 2012 at 08:33 AM.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: excel macro - reference to multipple cells

    probably you should use the worksheet change event
    vb Code:
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. If Target.Column = 2 Then
    3.     If IsEmpty(Target.Offset(, -1)) Then Target.Offset(, -1) = Date
    4. End If
    5. End Sub
    when you change and leave Bx, Ax will have the current date, if previously empty, you can test some value is in target (Bx) if required
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    9

    Re: excel macro - reference to multipple cells

    Hi, thanks for the help it works perfetly.
    Since I am the begginer I am asking for some more help.

    I also need a code that will insert region into column D if the cell is empty and after you insert telephone number into column C. For example if your tel number starts with 01, the region should be "something", and if number starts with 02 region should be "something_else" and if.... up to 05

    I tried like this but does not work:
    Code:
    If Target.Column = 3 Then
    If IsEmpty(Target.Offset(, 1)) And (Target.Offset(, 1)) = "01") Then Target.Offset(, 1) = "something"
    If IsEmpty(Target.Offset(, 1)) And (Target.Offset(, 1)) = "02") Then Target.Offset(, 1) = "something_else"
    End if

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: excel macro - reference to multipple cells

    If IsEmpty(Target.Offset(, 1)) And (Target.Offset(, 1)) = "01")
    the same cell can not be empty AND contain 01 ever
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    9

    Re: excel macro - reference to multipple cells

    Quote Originally Posted by westconn1 View Post
    the same cell can not be empty AND contain 01 ever
    Ok I see.
    Then I tried like in the bellow code, but the problem is that the telephone numer must be exactly 01, 02...., in order to recognise it. It does not work if the number is 01/3254534, 02/34654654....
    I need the algorythem to recognise just the first two digits of the number.

    Code:
    If Target.Column = 3 Then
    If (Target.Offset(, 0) = "01") Then Target.Offset(, 1) = "notranjska"
    If (Target.Offset(, 0) = "02") Then Target.Offset(, 1) = "štajerska"
    If (Target.Offset(, 0) = "03") Then Target.Offset(, 1) = "savinjska"
    If (Target.Offset(, 0) = "04") Then Target.Offset(, 1) = "gorenjska"
    If (Target.Offset(, 0) = "05") Then Target.Offset(, 1) = "primorska"
    If (Target.Offset(, 0) = "07") Then Target.Offset(, 1) = "dolenjska"
    End If

  6. #6

    Thread Starter
    New Member
    Join Date
    Aug 2010
    Posts
    9

    Re: excel macro - reference to multipple cells

    I have allready figured it out.
    I tried MID and seems to work...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width