-
Feb 24th, 2012, 08:26 AM
#1
Thread Starter
New Member
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.
-
Feb 25th, 2012, 12:20 AM
#2
Re: excel macro - reference to multipple cells
probably you should use the worksheet change event
vb Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then If IsEmpty(Target.Offset(, -1)) Then Target.Offset(, -1) = Date End If 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
-
Feb 27th, 2012, 08:15 AM
#3
Thread Starter
New Member
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
-
Feb 27th, 2012, 03:20 PM
#4
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
-
Feb 27th, 2012, 05:51 PM
#5
Thread Starter
New Member
Re: excel macro - reference to multipple cells
Originally Posted by westconn1
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
-
Feb 27th, 2012, 05:59 PM
#6
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|