Posts by Sam261

I am running the following code which looks at two different cell values to determine if a date/time should be inserted into a third cell. I am receiving a Run-Time91 error and it is highlighting the "If On5.Value" line. This procedure is called inside another procedure which causes a change to the cells values. Any ideas on why I'm getting the error?

Dim wksht1 As Worksheet


Dim On5 As Range, On6 As Range, On7 As Range
Dim rng5 As Range, rng6 As Range, rng7 As Range
Dim stp5 As Range, stp6 As Range, stp7 As Range


Set wksht1 = Sheets("Nucomat_Dashboard")
Set On5 = Range("N13").Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole)
Set On6 = Range("N14").Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole)
Set On7 = Range("N15").Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole)
Set rng5 = wksht1.Range("AA2:AA9").Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlUp)
Set rng6 = wksht1.Range("AA11:AA16").Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlUp)
Set rng7 = wksht1.Range("AA19:AA24").Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlUp)
Set stp5 = wksht1.Range("AB2:AB9").Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlUp)
Set stp6 = wksht1.Range("AB11:AB16").Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlUp)
Set stp7 = wksht1.Range("AB19:AB24").Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlUp)
With Sheets("Nucomat_Dashboard")




If On5.Value = 0 And rng5.End(xlUp).Value = "*" Then
stp5.End(xlUp).Offset(1, 0).Value = Now()
Else
End If




If On6.Value = 0 And rng6.End(xlUp).Value = "*" Then
stp6.End(xlUp).Offset(1, 0).Value = Now()
Else
End If




If On7.Value = 0 And rng7.End(xlUp).Value = "*" Then
stp7.End(xlUp).Offset(1, 0).Value = Now()
Else
End If


End With


End Sub

Just found out if I changed this from a worksheet_selection change to worksheet_change event it works perfectly! If only everything in life was so easy. Thank you for the suggestions though. Appreciate it.

Unfortunately, I can use the worksheet change/calculate events because there will be other instances of these cells being zero throughout the day. It can only happen immediately after the user chooses either dayshift or nightshift.

I am running the following code and for some reason it is finding the 1st empty cell in a completely different range. For example, it goes to the 1st empty cell in the "AA19:AA24" range. I just ran it and it went to cell "AA20" because AA19 already had a value in it and every cell in the "AA11:AA17" range was empty. I'm sure it is something simple that I am missing but I just can't figure it out. Any ideas?

Sub LookatU6()
'
' Macro1 Macro
'
Dim wksht1 As Worksheet
Dim u6 As Range
Set wksht1 = Worksheets("Nucomat_Dashboard")
Set u6 = wksht1.Range("AA11:AA19").Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Offset(1, 0)
On Error Resume Next
With Sheets("Nucomat_Dashboard")
If Range("N14").Value = 0 Then
u6.Value = Now()
End If
End With


End Sub

I am running the following selection change event however it only works AFTER I select a different cell and then re-select the target cell. The target cell had a drop down list for the user to select either dayshift or nightshift. I tried to have it programmatically select a different cell before re-selecting the target however that ended up in an endless loop or sorts. Any ideas on how to get this to work immediately after the user make a selection?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("N4")) Is Nothing Then


If Range("N4").Value = "Dayshift" Then
If Range("N13").Value > 0 Then
Range("AA2").Value = "05:00"
Else
End If
If Range("N14").Value > 0 Then
Range("AA11").Value = "05:00"
Else
End If
If Range("N15").Value > 0 Then
Range("AA19").Value = "05:00"
Else
End If
End If
If Range("N4").Value = "Nightshift" Then
If Range("N13").Value > 0 Then
Range("AA2").Value = "17:00"
Else
End If
If Range("N14").Value > 0 Then
Range("AA11").Value = "17:00"
Else
End If
If Range("N15").Value > 0 Then
Range("AA19").Value = "17:00"
Else
End If
End If
End If


End Sub

Yes. The dashboard is the main page while the received page kind of hangs out in the background. What happens is there is data placed on the dashboard page, that data is searched for on the "received" page & when it's found, the value in column B should be changed depending on whether the option button is checked or not. For example, if the option button is selected, column B will be changed to "Manual Digestion-Acid Consumption-Retest". If the option is not selected, then column B will be changed to "Manual Digestion-Acid Consumption".

It is finding the correct row & making the changes as required however it is also changing the "header" cell in column B(cell B1). Any ideas?

Hi,

I'm pretty new at this and have received a lot of help through this forum but I need just a little more help with an issue I can't seem to figure out. I am running this code and it works fine other than in addition to changing the cell in column B of the found row, it also changes the value in cell B1. In that cell I have a name of "Department" which should not be changed. The only value that should be changed is in the cell 2 columns to the left of the found cell value. Can anyone tell me what I have wrong here?

Here is my code:

Private Sub CommandButton1_Click()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
Dim FindString As String
Dim rng As Range
Dim d1 As Double
Dim d2 As Double
Set Sheet = Sheets("Manual_Dashboard")
d1 = Range("N12").Value
d2 = Range("N14").Value
For Each Cell In Range("D4:D43")
FindString = Cell.Value
With Sheets("Man_Received").Range("D:D")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlDown, _
MatchCase:=False
If Not rng Is Nothing Then
Application.Goto rng, True
If CheckBox1.Value = False Then
rng.Offset(0, -2).Value = "Manual Digestion-Acid Consumption"
Range("N12") = d1 + Range("D3").Value
Else
rng.Offset(0, -2).Value = "Manual Digestion-Acid Consumption-Retest"
Range("N14") = d2 + Range("D3").Value
End If
End If
End With
Next
With Sheets("Manual_Dashboard")
Range("D4:D43").ClearContents
End With
Sheet.Activate
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
End Sub

Did that with no luck. I did however remove the password protections and it started working again. So instead of protecting the sheet, I just applied conditions to the critical cells and that seems to be working. Thank you for taking the time to look.

Hi,

I have been running this macro which worked great until yesterday. It copies a range & creates a temporary jpg which is supposed to be pasted into the body of an email however since yesterday it has been pasting an old jpg image instead of the current one. I stepped into the code & ran it to the line that copies the range and it is working correctly up to that point. I verified by running it until after the "copy" line, then opening a blank word file & pasting. It is pasting the currently copies image. However if I run the code, it still pasted an old image from 5 days ago to be exact. Can anyone help me out on this?

That did it! Wow, I have been struggling for 2 days with this one task. Thank you so much!!!

Hello,

I am struggling to find a code that will delete the entire row if there is a value present in column C. I have attached a sample of what I have so far. This sub will be called by another sub which is not shown in the sample. Can anyone help me out with this please?

I am having the hardest time with copying and pasting which shouldn't be so difficult. This code is only pasting the # of used cells in the range when it should be copying whatever values are in that range"D4:D43". Also, this is a dynamic range meaning that not all rows will contain data and therefore not all rows will need to be copied each time. Can anyone tell me where I am going wrong here?

Dim rngND As Range
Dim lCurr As Long
Dim lRow As Long
Dim eRow As Long
Dim Last As Long
Dim shtRec As Worksheet
Dim Times As Worksheet
Dim shtND As Worksheet
Dim wbk1 As Workbook
Set shtRec = Sheets("Received")
Set shtND = Sheets("Nucomat_Dashboard")
Set wbk1 = Workbooks.Open("H:\users\Scoti\Timer.xlsx")
Set Times = wbk1.Sheets("Times")
lRow = Application.WorksheetFunction.CountA(shtRec.Range("$A:$A")) + 1
eRow = Application.WorksheetFunction.CountA(Times.Range("$A:$A")) + 1
Set rngND = shtND.Range("D4:D43" & Last).End(xlUp).Rows
lCurr = WorksheetFunction.CountA(rngND)
If lCurr = 0 Then
MsgBox "There do not appear to be any samples to assign.", vbInformation, "Insufficient Data"
Exit Sub
End If
With shtND
rngND.Copy
End With
With Times
.Cells(eRow, 1).PasteSpecial xlPasteValues
.Cells(eRow, 3).Value = Format$(Now(), "mm-dd hh:mm")
wbk1.Close savechanges:=True
End With
Call Load6
Do Until lCurr = 0

I will have to test that on my other macro and see. It seems to be working fine. Thanks for the info.

I am using the "nn" for minutes in another macro that is working well. What is the difference between the two?

I am unable to attach the entire workbooks due to their size.

I have a macro that is supposed to search for a range of values in workbooks range k4:k43 and when it finds those in workbook1 column B, it puts a timestamp in column D of the same row. With this code I am getting a Compile Error Next Without For error. Can anyone help me out on this please?

I need it to find the ID's on the Received sheet and move them to the Completed sheet. Thanks for the feedback.

This is a tracker workbook which follows samples through a process using their sample ID tag. On the main “Nucomat-Dashboard” tab, the operator has the ability to import incoming sample ID’s in cells D4:D36. The operator can then “load” them to a unit which will process them by clicking the corresponding button(Unit 5, etc.). Once the operator is finished with the samples, we need a way to show that they are done and have been forwarded to one of several other departments(Manual, Herzog, etc.) To do this, they scan the ID’s into cells K4:K36 on the main sheet and then click the corresponding button depending on which department will be receiving the samples.

I need a code that will find the list of ID’s on “Nucomat-Dashboard” sheet in cells K4:K36 in the “Received” sheet and move them to the “Completed” sheet under the appropriate column depending on which button is pressed on the “Nucomat-Dashboard” sheet. The formatted ID is one column to the right of the ID that will need to be moved.

Currently, the code copies the list and pastes it in the next available cell under the correct column on the “Completed” sheet however I really need it to move them from the “Received” to “Completed” sheet to prevent the “Received” sheet from having outdated information.

Then anyone can locate a sample by clicking on the “Sample Finder Magic Machine” button which brings up a user form to fill out. This part works great so I just need a way to move the ID’s from one sheet to the other instead of copy and paste.

I am still having trouble. So what I would like to do is include a step in the module that controls the "Outgoing Sample Log" area of the sheet that deletes the cells with matching data on the "Received" sheet. For example, when data is typed into the K4:K36 cells and the button "Manual" is clicked it will copy those values onto the Completed sheet. I would like to add that step of deleting them from the Received sheet at the same time. Is there a code line that could be inserted to do that?