Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TugboatEng on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!
Search results for query: *
- Users: onlyadrafter
- Order by date
-
VBA CODE - Tracking work hours
Hello, Does this work as expected? Column N needs to be empty though. Sub TRACK_HOURS() Application.ScreenUpdating = False 'PUT WEEK NUMBER IN COL N For MY_ROWS = 10 To Range("A" & Rows.Count).End(xlUp).Row Range("N" & MY_ROWS).Formula = "=weeknum(A" & MY_ROWS & ",2)" Next...- onlyadrafter
- Post #9
- Forum: Microsoft VBA - Visual Basic for Applications
-
VBA CODE - Tracking work hours
Hello, Oops. Just spotted that. The formula in M10 should be: =IF(AND(O9>40,O10>40),K10,IF(O10>40,O10-40,0)) copy this down as far as required. The reason I have blank cells, is that in the FILE, OPTIONS, ADVANCED. Display Options for this Worksheet, I have unchecked the display zeros...- onlyadrafter
- Post #6
- Forum: Microsoft VBA - Visual Basic for Applications
-
VBA CODE - Tracking work hours
Hello, You are correct, I did mean Cell A10, not C10. Have attached the file I created. I also copied the data down to try and replicate what you found, but it still appears to give correct results. ---------------------------------- Hope this helps. ---------------------------------- been...- onlyadrafter
- Post #4
- Forum: Microsoft VBA - Visual Basic for Applications
-
VBA CODE - Tracking work hours
Hello, If you can add two columns (to make this easier) then you can do the following: In N10 enter this formula: =WEEKNUM(C10,2) In O10 enter this formula: =SUMIF($N$10:N10,WEEKNUM(C10,2),$K$10:K10) In L10 enter this formula: =IF(O10>40,K10-M10,K10) In M10 enter this formula...- onlyadrafter
- Post #2
- Forum: Microsoft VBA - Visual Basic for Applications
-
Need Conditional Formatting help please
Hello, select the cells, select CONDITIONAL FORMATTING and select Cell Value is, NOT EQUAL TO, MWZ-6006, format as required. ---------------------------------- Hope this helps. ---------------------------------- been away for quite a while but am now back- onlyadrafter
- Post #2
- Forum: -Engineering spreadsheets
-
VBA -- Executing One Macro for Multiple Referenced Rows
Hello, This code replicates your code for Row 9. I have then made some assumptions: Column C on multi test has data every other row so the next macro copies: C11 to H15:L15 O11 to L23:O23 O10 to P23 P11 to L24:O24 P10 to P24 Sub COPY_OVER() Application.ScreenUpdating = False With...- onlyadrafter
- Post #3
- Forum: Microsoft VBA - Visual Basic for Applications
-
Drag Formula to increment worksheet
Hello, if you are still going with the multi sheet option, there are a couple of ways you can still get the sheet tab names and the link to the cell Sub GET_TAB_NAMES() For MY_SHEETS = 2 To ActiveWorkbook.Sheets.Count Cells(MY_SHEETS + 4, 11).Value = Sheets(MY_SHEETS).Name &...- onlyadrafter
- Post #7
- Forum: -Engineering spreadsheets
-
Drag Formula to increment worksheet
Hello, no need for two cells, as long as your tabs are Sheet2, Sheet3 etc then on the index page in Cell K6 enter this formula: =INDIRECT("Sheet"&ROW()-4&"!$F$9") and copy down as far as required ---------------------------------- Hope this helps. ---------------------------------- been...- onlyadrafter
- Post #3
- Forum: -Engineering spreadsheets
-
Hide Worksheet Based on Cell Value
Forgot to say that the code does not take into account any error checking. It will also fail if the sheet names do not exactly match the list in the Trade List spreadsheet. In your code, doesn't the set rng code cause problems if there are gaps in Column E? Private Sub Worksheet_Change(ByVal...- onlyadrafter
- Post #7
- Forum: -Engineering spreadsheets
-
Hide Worksheet Based on Cell Value
Hello, The code you have runs when you activate the macro. If you want the code to run when you enter a 0 or a 1 in column E then you can use the code below. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Selection, Range("E12:E110")) Is Nothing Then Exit Sub...- onlyadrafter
- Post #5
- Forum: -Engineering spreadsheets
-
Can you Add a drop down list within an if statement?
Hello, don't seem to be able to access your file from the link. So have created a file for you to view. ---------------------------------- Hope this helps. ---------------------------------- been away for quite a while but am now...- onlyadrafter
- Post #8
- Forum: -Engineering spreadsheets
-
Can you Add a drop down list within an if statement?
Hello, AS you have renamed it, you have not put the macro in the correct place. When you go to the VB code window, you need to look in Microsoft Excel Objects under your spreadsheet name. Here you will find a list of the sheets/tabs in you spreadsheet. Find the one that has your drop downs...- onlyadrafter
- Post #6
- Forum: -Engineering spreadsheets
-
Can you Add a drop down list within an if statement?
Hello, can be done via macro. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" And Target.Value = "YES" Then With Range("A2").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _...- onlyadrafter
- Post #2
- Forum: -Engineering spreadsheets
-
How do you use the Index formula using two criteria to return a value from a table in excel
Hello, how about (in F2) =INDEX($L$3:$L$326,MATCH(C3&D3,$I$3:$I$326&$J$3:$J$326,0)) and enter with CTRL + SHIFT + ENTER then copy down as far as required. ---------------------------------- Hope this helps. ---------------------------------- been away for quite a while but am now back- onlyadrafter
- Post #7
- Forum: -Engineering spreadsheets
-
Conditional format based on values, characters and spaces (feet and fractional inches)
Hello, Have tried this code on a few different entries, and it seems to work. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then If Format(Target.Value, "0" & "'-" & "0") <> Target.Value Then MsgBox "WRONG"...- onlyadrafter
- Post #4
- Forum: -Engineering spreadsheets
-
Restricting columns
Hello, this code can rename named ranges, not sure if it will work though. Sub named_region() MY_SOURCE = InputBox("Enter Source Sheet Name") Sheets(MY_SOURCE).Range("AA1").Select With Selection.ListNames For MY_ROWS = 1 To Range(("AA") &...- onlyadrafter
- Post #4
- Forum: -Engineering spreadsheets
-
EXCEL - Conditional Format based on another cell?
Hello, should have mentioned this only works when cell A1 is changed. I guess you have a formula. Try this Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect Select Case Range("A1").Value Case "GLOBAL" Range("C7").Locked = True...- onlyadrafter
- Post #10
- Forum: -Engineering spreadsheets
-
EXCEL - Conditional Format based on another cell?
Hello, you originally asked for a cell to be locked. This can be done, although your sheet protection and cell protection could affect the outcome. I have removed cell protection from all cells and have password protected the worksheet with no password. This code needs to go into the relevant...- onlyadrafter
- Post #8
- Forum: -Engineering spreadsheets
-
meaningful part numbers
Hello (again). Sorry above formula not accurate try =TEXT((A1*1000),"000")&TEXT((B1*1000),"000")&TEXT((C1*1000),"000")&TEXT((D1*1000),"000") ---------------------------------- Hope this helps. ---------------------------------- been away for quite a while but am now back- onlyadrafter
- Post #11
- Forum: -Engineering spreadsheets
-
meaningful part numbers
Hello, not sure what the exact result you require, but something on the lines of: =TEXT(VALUE(A1*1000&B1*1000&C1*1000&D1*1000),"000000000000") is this of any use? ---------------------------------- Hope this helps. ---------------------------------- been away for quite a while but am now back- onlyadrafter
- Post #10
- Forum: -Engineering spreadsheets
