Try this
This message is appearing only if the range of row is empty
Posts by elsuji
-
-
Try this
Dear Sobi,
I am getting the following error message while running this code only for average property
-
I tried this following doe for rank. But all the range R3:R & last row it is printing same value.
Van you please tell me what is the mistake on this code
-
Sorry. It is my mistake. I forget to upload link.
I used the following code.
Display MoreCode- '// for calculating Average
- Dim lRow As Long, i1 As Long
- With DstWks
- lRow = .Range("B" & .Rows.Count).End(xlUp).row
- .Range("Q3:Q" & lRow).Formula = _
- "=IF(ISERROR(AVERAGE(E3:P3)),"""",AVERAGE(E3:P3))"
- End With
- '//for finding no.of.position
- Dim lastrow As Long
- With DstWks
- lastrow = Cells(Cells.Rows.Count, "Q").End(xlUp).row
- .Range("R3:R" & lastrow).Value = "=RANK(Q3,$Q$3:$Q$" & lastrow & ",0)"
- End With
This working. But when i am selecting any cell on that range, the formula is displaying. I don't want to display the formula.
-
-
Did you try the macro I suggested in Post #4?
Dear Mumps ,
I tried your macro and modified little bit. Bellow is the modified code.
Display MoreCode- Sub CopyData()
- Application.ScreenUpdating = False
- Dim SrcWkb As Workbook, SrcWks As Worksheet, DstWkb As Workbook, DstWks As Worksheet, Val As String
- Dim i As Long, j, v1, v2, RngList As Object, strExtension As String
- Dim sFolderName As String, sFolderPath As String
- Dim m As Long
- Set DstWks = ThisWorkbook.Sheets("Dest")
- v1 = DstWks.Range("C3", DstWks.Range("C" & Rows.Count).End(xlUp)).Value
- sFolderPath = Environ("USERPROFILE") & "\Desktop\KRA SUmmary\"
- sFolderName = Format(Date, "yyyy") & "\" & Format(DateAdd("M", -1, Now), "mmmm yyyy") & "\"
- strExtension = Dir(sFolderPath & sFolderName & "*.xlsx*")
- m = Month(WorksheetFunction.EDate(Date, -1))
- Set RngList = CreateObject("Scripting.Dictionary")
- Do While strExtension <> ""
- Set SrcWkb = Workbooks.Open(sFolderPath & sFolderName & strExtension)
- v2 = Sheets(1).Range("C3", Sheets(1).Range("C" & Rows.Count).End(xlUp)).Value
- RngList.RemoveAll
- For i = 1 To UBound(v2, 1)
- Val = v2(i, 1)
- If Not RngList.Exists(Val) Then
- RngList.Add Key:=Val, Item:=i
- End If
- Next i
- For i = 1 To UBound(v1, 1)
- Val = v1(i, 1)
- If RngList.Exists(Val) Then
- DstWks.Cells(i + 2, m + 4) = Sheets(1).Cells(RngList(Val) + 2, m + 4)
- End If
- Next i
- SrcWkb.Close False
- strExtension = Dir
- Loop
- Application.ScreenUpdating = True
- End Sub
It is copying only the previous month data's (April Month) of current month.
But for checking purpose changed the month (before and after the current month) manually and run the code, is not working.
Can you please tell me where is the mistake and what is the correction have to do on this code
-
Dear Roy,
I checked your code. It is copying last updated column on source file and in destination file it is paste the last empty column. in that case it will keep on filling same values again and again for all the columns until i update the next data on my source file.
But my requirement is first it should check the heading which column the previous month is available and copy paste values on that particular month. So the values are not repeated on my destination file.
Let me explain you;
Every month i will receive the updated files(Source files) from all the region and i will consolidate that files to single file(destination file).
For example, April month data i will receive on or before 15th of May month. When i am press the copy data button it should copy only April 20 values from source file and paste to April 20 on destination file. it wont do any changes on Jan to March value. If there is any blank on Jan to March let it be blank.
Again May month data i will receive on or before 15th of June month. When i am press the copy data button it should copy copy May 20 values from source file and paste to May 20 on destination file. it wont do any changes on Jan to April value. If there is any blank on Jan to April let it be blank.
-
Dear Team,
I am entering my engineers mark on columns from E to P. whenever I am entering values it want to calculate average of column E to P and the average value should update in column Q.
Also i want to calculate the no.of position for that average value.
I attached my sample file here for your reference.
Kindly help me to do this
-
Dear Roy,
I modified Mr.Mumps code as per the requirement and checked. Bellow is the modified code
Display MoreCode- Sub CopyData()
- Application.ScreenUpdating = False
- Dim SrcWkb As Workbook, SrcWks As Worksheet, DstWkb As Workbook, DstWks As Worksheet, Val As String
- Dim i As Long, j, v1, v2, RngList As Object, strExtension As String
- Dim sFolderName As String, sFolderPath As String
- Dim m As Long
- Set DstWks = ThisWorkbook.Sheets("Dest")
- v1 = DstWks.Range("C3", DstWks.Range("C" & Rows.Count).End(xlUp)).Value
- sFolderPath = Environ("USERPROFILE") & "\Desktop\KRA SUmmary\"
- sFolderName = Format(Date, "yyyy") & "\" & Format(DateAdd("M", -1, Now), "mmmm yyyy") & "\"
- strExtension = Dir(sFolderPath & sFolderName & "*.xlsx*")
- m = Month(WorksheetFunction.EDate(Date, -1))
- 'm = Month(WorksheetFunction.EoMonth(Now, -1))
- Set RngList = CreateObject("Scripting.Dictionary")
- Do While strExtension <> ""
- Set SrcWkb = Workbooks.Open(sFolderPath & sFolderName & strExtension)
- v2 = Sheets(1).Range("C3", Sheets(1).Range("C" & Rows.Count).End(xlUp)).Value
- RngList.RemoveAll
- For i = 1 To UBound(v2, 1)
- Val = v2(i, 1)
- If Not RngList.Exists(Val) Then
- RngList.Add Key:=Val, Item:=i
- End If
- Next i
- For i = 1 To UBound(v1, 1)
- Val = v1(i, 1)
- If RngList.Exists(Val) Then
- DstWks.Cells(i + 2, m + m) = Sheets(1).Cells(RngList(Val) + 2, m + m)
- End If
- Next i
- SrcWkb.Close False
- strExtension = Dir
- Loop
- Application.ScreenUpdating = True
- End Sub
It is copying only the previous month data's of current month.
But for checking purpose changed the month (before and after the current month) manually and run the code, is not working.
Can you please tell me where is the mistake on this code
-
My requirement is
when ever i am run the code it should copy the previous month values of current month from source file and paste that to same previous month column on Destination sheet.
For example, Current month is May. If i run the code it should copy April month data from my source file and paste to master file on April month column.
And for checking the source file i had modified code toMy source files are downloaded from outlook mail. for that i had created code already. Please look at the attachment.
Kindly do the modification as per my requirement
-
OK. I will be waiting for you
-
ok. i will try with 2016
-
I have to close it by this week end. That's what i asked you
-
Dear Roy,
Can you please help me
-
MS OFFICE 2013
-
I tried this =CONCATENATEX(Range, Range[Source], ",")
once i add this to VALUE field i am getting following error
-
I am having power pivot available. I am trying through that also. But not working
-
Can any one reply me pls. It is urgent
-
Dear Team,
I had created pivot table . On my pivot table in VALUES field i add column A Which is heading "SOURCE". In column A the followings are available (CALL, NONE, TIME, VISIT)
Once i add the SOURCE in pivot table it will count and display the number. But i want to display the name instead of no.
Can any one help me how to display the names on the pivot table
Also explain me how to do this
I am attaching my file here for your reference
-
Dear Roy,
I add new button for download the files from mail and create folder and save all files month wise.
For consolidating it, the files to be taken from the current month folder.
I am attaching new file here.
Please check the attached file and help me to consolidate the list