Posts by ljoseph
-
-
-
With the following code, I am trying to define dynamic named formulas.
The formula AVG1 should start at "e5" (cells(5,5)). AVG2 should start at "O5" which is cells(5,15). I don't know how to implement the cells in the formula. Following code gives error:CodeSub na() Dim r As Integer For r = 1 To 2 ThisWorkbook.Names.Add Name:="avg" & Format(r), RefersTo:="=OFFSET(Cells(5,5+(r-1)*10),(Sheet1!$BP$43-1)*18,Sheet1!$BO$43,16,1)", Visible:=True Next r End Sub
Appreciate your help -
I have the following code to copy data from a workbook to another workbook. Once the "from workbook" is opened, it is assigned to workbook "abook" and certain operations are done on it.The main code is on workbook "mbook"
using this section of code , I am doing the copy and pastespecial.Codeabook.Worksheets("analysis").Range("DW27:EL27").Copy abook.Worksheets("analysis").Range("FJ19").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Application.CutCopyMode = False Selection.Copy Destination:=mbook.Worksheets("sheet1").Cells(2 + (meas - 1) * 18, 2 + temp)
For some reason the line:
abook.Worksheets("analysis").Range("FJ19").Select
gives errors....Basically, I want to copy and paste the trasposed value...I think it can be implemented in a better way...
even though mbook is active the status bar with the following code is not displaying anything...
Application.StatusBar = "Copying " & temp & measAppreaciate your help.
This is the wholde code
Code
Display MoreSub compare() Dim datafiles As Variant, numberoffiles As Integer, i As Integer, mbook As Workbook, abook As Workbook Dim meas As Integer, n As Integer, temp As Integer Application.DisplayAlerts = False Application.ScreenUpdating = False Set mbook = ThisWorkbook datafiles = Application.GetOpenFilename(FileFilter:="XLS Files,*.XLS", FilterIndex:=1, Title:="Select GMSK WBN Files", MultiSelect:=True) 'numberoffiles = UBound(datafiles) i = 1 Workbooks.OpenText Filename:= _ datafiles(i), Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _ :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _ False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array _ (1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8 _ , 1), Array(9, 1)) Set abook = ActiveWorkbook mbook.Activate For meas = 1 To 6 abook.Worksheets("Analysis").Range("dc7") = meas For temp = 1 To 3 Application.StatusBar = "Copying " & temp & meas abook.Worksheets("Analysis").Range("dd7") = temp abook.Worksheets("analysis").Range("DW27:EL27").Copy abook.Activate abook.Worksheets("analysis").Range("FJ19").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Application.CutCopyMode = False Selection.Copy Destination:=mbook.Worksheets("sheet1").Cells(2 + (meas - 1) * 18, 2 + temp) abook.Worksheets("analysis").Range("DW29:EL29").Copy abook.Activate abook.Worksheets("analysis").Range("FJ19").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Application.CutCopyMode = False Selection.Copy Destination:=mbook.Worksheets("sheet1").Cells(2 + (meas - 1) * 18, 7 + temp) Next temp Next meas abook.Worksheets("analysis").Range("dc7") = "=dc17" abook.Worksheets("analysis").Range("dd7") = "=dd17" abook.Close mbook.Activate End Sub
-
I have a macro that opens excel file from a folder, look for "Fail" or "pass" in the file and copy to another folder. I am using filesystemobject.copy folder for this. In the copied folder, is it possible to highlight the failed files...say by making the file name red of bold....
Appreciate your help.
Thanks
Leyo
-
Re: change font size of comment
I tried the following code and works well.
Code
Display MoreFor Each com In ActiveSheet.Comments With com.Shape .TextFrame.AutoSize = True .AutoShapeType = msoShapeRoundedRectangle With .TextFrame.Characters.Font .Name = "Teriminal" .Size = 14 .Bold = True End With End With Next com
Thanks
-
Re: Average of each line of a range
Thanks
It is working well.
-
I have a named formula "yavls" using offset funtion. Depeding on some selction in the worksheet, the range yvals will be "bd6:bm34" or "BN6:Bw34" etc..
In range "ba6:ba34" I want to have average of yvals. I mean in cell BA6 the average of first row of "yvals" and in Ba7, average of second row of yavls and so on. I am trying to do this using worksheetfunction...not successful.Appreciate your help.
-
Re: error with named formulas
I have noticed that if I change "Analysis_All Channel" to "Analysis_Allchannel", it works fine. For some reason, it is not able to take the space.
Thanks -
Hello
In my workbook, there are two sheets "Analysis_BMT" and "Analysis_All Channels". With the following code,I am trying to define a named formula in worksheets "Analysis_All Channel". I am getting the error "Name is not valid". But the code works well when I try to do it with worksheets " Analysis_BMT".
What is the mistake here?
CodeWith ThisWorkbook.Names .add Name:="Analysis_All Channel!xvals", RefersTo:="=offset(Analysis_All Channel!$BD$6,0,Analysis_All Channel!$BC$5,Analysis_All Channel!$AZ$4,Analysis_All Channel!$AZ$2)", Visible:=True '.add Name:="Analysis_All Channel!xvalk", RefersTo:="=Analysis_All Channel!Chartmin+Analysis_All Channel!Spacing*(ROW(OFFSET(Analysis_All Channel!$A$1,0,0,Analysis_All Channel!numberofpoints+1,1))-1)", Visible:=True End With
-
-
I have defined two named formulas PL0 and PL1 using the following code. Now, I am trying to define another named formula "md" which is a union of PL0 and PL1. But, the code is not working. what is the mistake here?
CodeFor r = 0 To 1 ThisWorkbook.Names.add Name:="Analysis_BMT!PL" & Format(r), RefersTo:="=OFFSET(Analysis_BMT!sc,Analysis_BMT!vskip-Analysis_BMT!$az$4+1," & r + 1 & "+Analysis_BMT!plskip,counta(index(Analysis_BMT!pldata,0," & r + 1 & " +Analysis_BMT!plskip)),1)", Visible:=True Next r ThisWorkbook.Names.add Name:="Analysis_BMT!md", RefersTo:="= Union(Range("Analysis_BMT!PL0"), Range("Analysis_BMT!PL1"))", Visible:=True
-
Hello
In the attached sheet, I have data in the range("fa9:fk84"). With the following code, conditional formatting is applied to range(fj9:fj84).Now, I want the format of range(fa9:fi9) that of fj9,format of range(fa10:Fi10) taht of fj10 etc....I mean, the color index, pattern, bold true etc.. How to do code it?
Appreciate your help.Code
Display MoreRange("fj9:fj84").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="0" With Selection.FormatConditions(1).Font .Bold = True .Italic = False .ColorIndex = 2 End With With Selection.FormatConditions(1).Interior .ColorIndex = 3 .Pattern = xlSolid End With Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="=$FE$3", Formula2:="=$FF$3" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 9 End With Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ Formula1:="=$FE$4", Formula2:="=$FF$4" With Selection.FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 7 End With End Sub
-
-
Re: change font size of comment
Thanks
I tried the following as per your suggestion and getting error "Method select of object shape failed"
What is the mistake I am making here?CodeRange("dp10").AddComment "Worst Cpk at " & add.Offset(-1, 0).Value & " Corrseponding Cpk with Spec of " & Range("dj73") & " dBm is " & add.Offset(0, 6).Value Range("dp10").Comment.Shape.AutoShapeType = msoShapeRoundedRectangle Range("dp10").Comment.Shape.Select True With Selection .Font.Size = 11 End With
-
Hello
In my VBA code, I am putting comments on some cells using the command "add comment". The comment is having default font size. How to change the font size to say 11 using VBA?. I want to know how to change the size of the comment box also.
Apprecaite your help.
-
Re: strange with values
Thanks Sir
Just curious to know what is the reason for this?
Apprecaite your help.
Thanks
-
Please have a look on the attached sheet. I have data from range "K13:l19"
on cell L19, I have the formula L13-L17 which gives the resut 9.57. I copy this cell and paste value in L22. The value displayed in the formula bar is 9.56999999999999. Has the formula K 19 = K13-K17 and get the value 15.98. Copied cell K19 and paste the value in K22. The formula bar displays 15.98. Why it is different in two cases. In my main sheet, I am using find method to find 9.57. Since the value in the cell is 9.56999999999999...getting errors.
Appreciate your help.Thanks
-
Re: looping to find a value
To be more specific ( I have the whole code
If the code is not able to find the wccpk ( For example if the find function is set for lookin formulas ,not able to find the value), the error (error code 91) is always in the statement after look6:.
Worksheets("analysis").Cells(10, 119).AddComment "Worst Cpk _ " & Worksheets(measlist(10)).Cells(add.Row - 2, 65) & " @ " & Worksheets(measlist(10)).Cells(add.Row - 2, 67) & " @ " & add.Offset(-1, 0).Value & " @ " & Worksheets(measlist(10)).Cells(add.Row - 2, 69)
why it always giving error in the above statement while there are other lines with "add comment"Code
Display MoreSub compliancemrix() Dim n As Integer, wccpk As Double, i As Integer, temp As Integer, add As Range, rng2 As Range Dim c As Integer, k As Integer, temper As String, h As Integer, wc As Double Application.DisplayAlerts = False Application.ScreenUpdating = False sheets Worksheets("analysis").Range("do22:dr44").ClearContents Worksheets("analysis").Range("do22:dr44").ClearComments On Error GoTo kkk1 For n = 1 To 23 Worksheets(measlist(n)).Activate Range("bl1") = "WC Margin" Range("BM1").FormulaR1C1 = "=MIN(R[14]C:R[14]C[10],R[26]C:R[26]C[10],R[38]C:R[38]C[10])" Range("BL2") = "Min Cpk" Range("BM2").FormulaR1C1 = "=MIN(R[6]C:R[6]C[10],R[18]C:R[18]C[10],R[30]C:R[30]C[10])" Next n n = 10 Worksheets(measlist(n)).Activate Range("bp1") = "WC Margin" Range("bq1").FormulaR1C1 = "=MIN(R[50]C[-4]:R[50]C[6],R[62]C[-4]:R[62]C[6],R[74]C[-4]:R[74]C[6],R[86]C[-4]:R[86]C[-3],R[98]C[-4]:R[98]C[-3],R[110]C[-4]:R[110]C[-3])" Range("BP2") = "Sep Cpk" Range("Bq2").FormulaR1C1 = "=MIN(R[42]C[-4]:R[42]C[6],R[54]C[-4]:R[54]C[6],R[66]C[-4]:R[66]C[6],R[78]C[-4]:R[78]C[-3],R[90]C[-4]:R[90]C[-3],R[102]C[-4]:R[102]C[-3])" For n = 1 To 23 Select Case n Case 10 Worksheets("analysis").Cells(21 + n, 119) = Worksheets(measlist(n)).Range("Bq2") Worksheets("analysis").Cells(21 + n, 121) = Worksheets(measlist(n)).Range("Bq1") Case Else Worksheets("analysis").Cells(21 + n, 119) = Worksheets(measlist(n)).Range("BM2") Worksheets("analysis").Cells(21 + n, 121) = Worksheets(measlist(n)).Range("Bm1") End Select Next n 'Looking for worstcase cpk For n = 1 To 23 c = 0 Select Case n Case 10 wccpk = Worksheets("analysis").Cells(21 + n, 119) For k = 1 To 6 Set rng = Worksheets(measlist(n)).Range(Worksheets(measlist(n)).Cells(44 + c, 65), Worksheets(measlist(n)).Cells(44 + c, 75)) Set add = rng.Find(wccpk, , LookAt:=xlWhole, MatchCase:=True) If Not add Is Nothing Then GoTo look1 c = c + 12 Next k look1: Worksheets("analysis").Cells(21 + n, 119).AddComment "Worst Cpk _ " & Worksheets(measlist(n)).Cells(add.Row - 2, 67) & " @ " & add.Offset(-1, 0).Value & " @ " & Worksheets(measlist(n)).Cells(add.Row - 2, 69) Worksheets("analysis").Cells(21 + n, 120) = add.Offset(7, 0) Case Else wccpk = Worksheets("analysis").Cells(21 + n, 119) For k = 1 To 3 Set rng = Worksheets(measlist(n)).Range(Worksheets(measlist(n)).Cells(8 + c, 65), Worksheets(measlist(n)).Cells(8 + c, 75)) Set add = rng.Find(wccpk, , LookAt:=xlWhole, MatchCase:=True) If Not add Is Nothing Then GoTo look2 c = c + 12 Next k look2: Worksheets("analysis").Cells(21 + n, 119).AddComment "Worst Cpk _ " & Worksheets(measlist(n)).Cells(add.Row - 2, 67) & " @ " & add.Offset(-1, 0).Value & " @ " & Worksheets(measlist(n)).Cells(add.Row - 2, 69) Worksheets("analysis").Cells(21 + n, 120) = add.Offset(7, 0) End Select Next n 'Looking for worstcase margin For n = 1 To 23 c = 0 Select Case n Case 10 wc = Worksheets("analysis").Cells(21 + n, 121) For k = 1 To 6 Set rng = Worksheets(measlist(n)).Range(Worksheets(measlist(n)).Cells(51 + c, 65), Worksheets(measlist(n)).Cells(51 + c, 75)) Set add = rng.Find(wc, , LookAt:=xlWhole, MatchCase:=True) If Not add Is Nothing Then GoTo look3 c = c + 12 Next k look3: Worksheets("analysis").Cells(21 + n, 121).AddComment "Worst margin _ " & Worksheets(measlist(n)).Cells(add.Row - 9, 67) & " @ " & add.Offset(-8, 0).Value & " @ " & Worksheets(measlist(n)).Cells(add.Row - 9, 69) Worksheets("analysis").Cells(21 + n, 122) = add.Offset(-7, 0) Case Else wc = Worksheets("analysis").Cells(21 + n, 121) For k = 1 To 3 Set rng = Worksheets(measlist(n)).Range(Worksheets(measlist(n)).Cells(15 + c, 65), Worksheets(measlist(n)).Cells(15 + c, 75)) Set add = rng.Find(wc, , LookAt:=xlWhole, MatchCase:=True) If Not add Is Nothing Then GoTo look4 c = c + 12 Next k look4: Worksheets("analysis").Cells(21 + n, 121).AddComment "Worst margin _ " & Worksheets(measlist(n)).Cells(add.Row - 9, 67) & " @ " & add.Offset(-8, 0).Value & " @ " & Worksheets(measlist(n)).Cells(add.Row - 9, 69) Worksheets("analysis").Cells(21 + n, 122) = add.Offset(-7, 0) End Select Next n wccpk = 100 For n = 1 To 23 Select Case n Case 10 If Worksheets(measlist(n)).Range("Bq2") < wccpk Then wccpk = Worksheets(measlist(n)).Range("Bq2") Case Else If Worksheets(measlist(n)).Range("BM2") < wccpk Then wccpk = Worksheets(measlist(n)).Range("BM2") End Select Next n Worksheets("analysis").Activate Worksheets("analysis").Range("do10").ClearComments Worksheets("analysis").Range("do10").ClearContents Worksheets("analysis").Range("do10") = wccpk Set rng2 = Worksheets("analysis").Range("dw25:eg25") Set add = Nothing For i = 1 To 23 Range("dc7") = i For temp = 1 To 3 Range("dd7") = temp Set add = rng.Find(wccpk, , LookAt:=xlWhole, MatchCase:=True) If Not add Is Nothing Then GoTo look5 Next temp Next i If add Is Nothing Then GoTo kkk1 look5: Worksheets("analysis").Cells(10, 119).AddComment "Worst Cpk _ " & Worksheets("analysis").Cells(add.Row - 2, 65) & " @ " & Worksheets("analysis").Cells(add.Row - 2, 67) & " @ " & add.Offset(-1, 0).Value & " @ " & Worksheets("analysis").Cells(add.Row - 2, 69) Range("dp10") = add.Offset(7, 0).Value Range("dd7") = 1 Range("dq10") = add.Offset(0, 0).Value GoTo kkk2: kkk1: For k = 1 To 6 Set rng = Worksheets(measlist(10)).Range(Worksheets(measlist(10)).Cells(44 + c, 65), Worksheets(measlist(10)).Cells(44 + c, 75)) Set add = rng.Find(wccpk, , LookAt:=xlWhole, MatchCase:=True) If Not add Is Nothing Then GoTo look6 c = c + 12 Next k look6: Worksheets("analysis").Cells(10, 119).AddComment "Worst Cpk _ " & Worksheets(measlist(10)).Cells(add.Row - 2, 65) & " @ " & Worksheets(measlist(10)).Cells(add.Row - 2, 67) & " @ " & add.Offset(-1, 0).Value & " @ " & Worksheets(measlist(10)).Cells(add.Row - 2, 69) Worksheets("analysis").Range("dp10") = add.Offset(7, 0).Value temper = Worksheets(measlist(10)).Cells(add.Row - 2, 67) Select Case temper Case "AMBIENT" h = 0 Case "COLD" h = 12 Case "HOT" h = 24 End Select Worksheets("analysis").Range("dq10") = add.Offset(-h, 0).Value kkk2: Range("dc7") = "=dc17" Range("dd7") = "=dd17" Range("de7") = "=de17" Application.StatusBar = "Completed Compliance Matrix" End Sub
-
The following code is part of a big code to find a particular value.
The value I am looking for is wccpk. For case 10 looking for in a specific range and other cases other range. The range keep on changing for each iteration.Take case n=1. If the code is not able to find the wccpk after looping k=1,2 and 3 will it excecute look2: or next n?. It is doing some weired things...Is there anything wrong in my code structure?
Code
Display MoreFor n = 1 To 23 c = 0 Select Case n Case 10 wccpk = Worksheets("analysis").Cells(21 + n, 119) For k = 1 To 6 Set rng = Worksheets(measlist(n)).Range(Worksheets(measlist(n)).Cells(44 + c, 65), Worksheets(measlist(n)).Cells(44 + c, 75)) Set add = rng.Find(wccpk, , LookAt:=xlWhole, MatchCase:=True) If Not add Is Nothing Then GoTo look1 c = c + 12 Next k look1: Worksheets("analysis").Cells(21 + n, 119).AddComment "Worst Cpk _ " & Worksheets(measlist(n)).Cells(add.Row - 2, 67) & " @ " & add.Offset(-1, 0).Value & " @ " & Worksheets(measlist(n)).Cells(add.Row - 2, 69) Worksheets("analysis").Cells(21 + n, 120) = add.Offset(7, 0) Case Else wccpk = Worksheets("analysis").Cells(21 + n, 119) For k = 1 To 3 Set rng = Worksheets(measlist(n)).Range(Worksheets(measlist(n)).Cells(8 + c, 65), Worksheets(measlist(n)).Cells(8 + c, 75)) Set add = rng.Find(wccpk, , LookAt:=xlWhole, MatchCase:=True) If Not add Is Nothing Then GoTo look2 c = c + 12 Next k look2: Worksheets("analysis").Cells(21 + n, 119).AddComment "Worst Cpk _ " & Worksheets(measlist(n)).Cells(add.Row - 2, 67) & " @ " & add.Offset(-1, 0).Value & " @ " & Worksheets(measlist(n)).Cells(add.Row - 2, 69) Worksheets("analysis").Cells(21 + n, 120) = add.Offset(7, 0) End Select Next n 'remaining part of code continues.