Posts by HELP99

How do I import in from power query the pages that has multiple pages
From webpage I insert url , I click inside the table, but import the first page only in power query

Storage Guide – Cargo Handling Corporation (chcl.mu)

I have search on the web

Scrape Data from Multiple Web Pages with Power Query • My Online Training Hub but it has not the start function

Storage Guide – Cargo Handling Corporation

1 [ 2 ][ 3 ][ 4 ][ 5 ][ 6 ][ 7 ][ 8 ][ 9 ] Next>>

let

Source = Web.Page(Web.Contents("https://www.chcl.mu/storage-guide/")),

Data0 = Source{0}[Data],

#"Changed Type" = Table.TransformColumnTypes(Data0,{{"ATP", type text}, {"VESSEL", type text}, {"CODE", type text}, {"VOYAGE", type text}, {"ACTUAL TIME OF ARRIVAL", type text}, {"DISCHARGE COMPLETE", type text}})

in

#"Changed Type"

Can anyone help how to import all the pages

Hello
Thanks working but why what was wrong with my code

I have a workbook customer A and criteria sheet .

Customer has a lot of data up 300000 rows .

From criteria sheet I have identical headers rows from both sheets .

Criteria sheets row 1 and row 2 represents the criteria range

At column BE of criteria sheet there are formulas which is related to range A1 to AY.

But Code not working

Output range A5 with partial criteria

Quote

Sub FILTER_1A()
'
' FILTER_1 Macro
'
'
'Range("C8:C10").Select
Sheets("CUSTOMERA").Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("CRITERIA").Range("BE1:DC2"), CopyToRange:=Range("A4:AY8"), Unique:= _
False
End Sub

Display More

I have a workbook with a specific sheet ("testa")with special characters (:/\?*[])? in a specific cell F2 value reads "MERCEND/FF." .

I have found a code below while googling but how to adjust for a specific sheet "MERCEND/FF." of the workbook. to rename the sheet by CELL F2 replacing the special character with either _or - or space

e.g "MERCEND FF." or "MERCEND-FF." or "MERCEND-FF." that is rename testa sheet to cell F2 value

Sub tabname() Dim ws As Worksheet For Each ws In Worksheets On Error Resume Next If Len(ws.Range("F2")) >0 Then ws.Name = ws.Range("F2").Value End If On Error Goto 0 If ws.Name <> ws.Range("F2").Value Then Msgbox ws.Name & " Was Not renamed, the suggested name was invalid" End If Next End Sub

Apolgize ,I am having some issue to insert tags

Now it worked thank you for assistance but one line was missing from your post 4 ,.
:drum::rock:

ChDir ("C:\TESTCLEAR\")

Tried workbook open and close , but still unsuccessful..

Message runtime error 1004

Hello

When tested files were open compile error variable not defined

Hello

I have a bunch of excel files (around 400files ) in a folder C:\ TESTCLEAR.

Looking a macro to clear all named ranges and break any link to the files .

Also to delete row 1 & 2 and column A and save file on same names .

due to limitations to attachement there are links that be found using the name manager

All files has a sheets named " Infos_Container" , the workbook may have other sheets but not related to " Infos_Container"

the macro to save the files in same folder .

Can if possible macro do it without opening the files.

Hi KjBox,

Following post 7 macro code , I was getting some issues .

As per attached the value should read 11308 in column F which is correct in applying macro , however I copy column F to I to check getting 11306 instead of 11308.

Could you have a look

Hello Pike

Code works marvelously corrected the typo error as per post 3

Thank you a lot

Hello Pike

As below , found typo error in red

Thanks for assistance.

Thanks

Sub M1modifiedformat()




    Dim x       As Long
    Dim arr()   As Variant
    Dim temp    As Variant
    Dim ws1     As Worksheet
    Dim ws2     As Worksheet
    Dim dic     As Object
  
    Const delim As String = "|"
  
    Set ws1 = Sheets("Input Datasheet")
    Set ws2 = Sheets("Product DataBase")
    Set dic = CreateObject("Scripting.Dictionary")




    Application.ScreenUpdating = False
  
    With ws2
        For x = 1 To .Cells(.Rows.Count, 2).End(xlUp).Row
            dic(Val(.Cells(x, 2).Value)) = .Cells(x, 4).Value & delim & .Cells(x, 5).Value
        Next x
    End With
  
    With ws1
        x = .Cells(.Rows.Count, 6).End(xlUp).Row
        arr = .Cells(2, 6).Resize(x - 1, 2).Value
      
        For x = LBound(arr, 1) To UBound(arr, 1)
            If dic.exists(.Cells(x + 1, 6).Value) Then
                temp = Split(dic(.Cells(x + 1, 6).Value), delim)
                arr(x, 1) = temp(0)
                arr(x, 2) = temp(1)
                Erase temp
            Else
                arr(x, 1) = Empty
                arr(x, 2) = Empty
            End If
        Next x
      
        .Cells[COLOR=#FF0000]([B]2,[/B][/COLOR] 8).Resize(UBound(arr, 1), UBound(arr, 2)).Value2 = arr
    End With
  
    Application.ScreenUpdating = True
  
    Set dic = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
    Erase arr
  
End Sub

I have uploaded the original file M1 macro ,need to adjust the vba code on the test amended column .xlsm file as per post1 2nd attachment
Thanks if someone can assist

I have a workbook with imput datasheet and Product database .

Using index and match vba code as below.

however due to change in format , the columns have move in both sheets imput and product database .

Looking assistance for help to change the code according the columns No
in the amended sheet column file.

I have attached the original one testxlsm and the amended xlsm which is the one in which the columns no have be moved .

Looking assistance for help to change the code according the columns No

in the amended sheet format

The original lookup in the third column reference imput data and extract as per reference data in product database that is column A and Column B of product database.

thanks if someone can assist

Sub M1()




    Dim x      As Long
    Dim arr()  As Variant
    Dim temp    As Variant
    Dim ws1    As Worksheet
    Dim ws2    As Worksheet
    Dim dic    As Object
  
    Const delim As String = "|"
  
    Set ws1 = Sheets("Input Datasheet")
    Set ws2 = Sheets("Product DataBase ")
    Set dic = CreateObject("Scripting.Dictionary")




    Application.ScreenUpdating = False
  
    With ws2
        For x = 1 To .Cells(.Rows.Count, 3).End(xlUp).Row
            dic(Val(.Cells(x, 3).Value)) = .Cells(x, 1).Value & delim & .Cells(x, 2).Value
        Next x
    End With
  
    With ws1
        x = .Cells(.Rows.Count, 3).End(xlUp).Row
        arr = .Cells(2, 1).Resize(x - 1, 4).Value
      
        For x = LBound(arr, 1) To UBound(arr, 1)
            If dic.exists(.Cells(x + 1, 3).Value) Then
                temp = Split(dic(.Cells(x + 1, 3).Value), delim)
                arr(x, 1) = temp(0)
                arr(x, 2) = temp(1)
                Erase temp
            Else
                arr(x, 1) = Empty
                arr(x, 2) = Empty
            End If
        Next x
      
        .Cells(2, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value2 = arr
    End With
  
    Application.ScreenUpdating = True
  
    Set dic = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
    Erase arr
  
End Sub

Tested post 7 works marvelously .

Thank you for assistance

The cell M2 is entered manually 11308

the cell E17 11307.92 is total of column E which is the real value but need to rounded to to nearest 0 that is 11308 which is at cell M2 entered manually.

Using rounding formula in column F round(cell value ,0) , , then copy and paste value only in same column F , the total cell F17 is 11306 not correct .

So I need to play with figures in cell F14 AND F15 ADD 1 to each cell so that total F 17= 11308 based on cell value M2

attached explanation sheet tab where column K explain what I am trying to do .

Note the value are sometimes more so I need to reduce where value rounded to nearest 0

e.g 12.57 if value is more adjust 12.

Hi KjBox

Tried this method, but indeed the issue comes with column F it still gives me the incorrect value as I need to copy it a csv file and upload on an interface , but in clearing the values , it says value does not match with physical imput , the interface value entered 11308 rounded , but when on excel sheet , it need to be rounded so that uploading tallies with physical imput .

physical imput 110308

excel sheet 11308 rounded but copy and paste value to the adjacent cell it gives 11306, this is my issue , sometimes with rounding its over the value or under value , so I have to adjust manually at random priority to round 0.50 in column E , and if under value increasesd by 0.50

Got a code but need to be based on M2.

Hope it helps

Sub _Adjust()     Dim r As Long, LastRow As Long, tot As Long     Randomize     LastRow = Range("E" & Rows.Count).End(xlUp).Row     tot = Application.Round(Range("C" & LastRow).Value, 0)     Do Until Range("E" & LastRow).Value = tot         DoEvents         r = Int((LastRow - 2) * Rnd + 2)         With Range("E" & r)             If .Value = Range("D" & r).Value Then                 .Value = .Value + IIf(Range("E" & LastRow).Value < tot, 1, -1)             End If         End With     Loop End Sub

I have a workbook which invoice values need to rounded based on cell value M2.

The column E represents the values of invoice and it total 11307.92 which represents the original value and column F is the the round value but on copy and paste special values on column F gives total amount 11306 instead of 11308.

So I have to pick up some cell values at random on column F so that total =11308 as cell M2.

However there are times when value might be more 11312 so I need to select at random on column F so that =11308 as example .

The values total varies , it can be more or sometimes less

Am looking a macro to do this as data is dynamic up to 3000 rows .

Thank you worked smoothly :rock:

I have a range of data on sheet analysis.
range of data column A to L
from ColumnA as reference to last data row.

how to sum each column starting from Column E to column L in vba macro

row 1 is the header