export – Exporting multiple lists (x1,y1…n) (x2,y2..n)of different lengths into same excel files separated by spaces of two columns between each (x,y) list?

I have multiple lists like this this with different lengths and different x, y values,

list1 = {{3., 1.49463}, {3.1, 1.49238}, {3.2, 1.49027}, {3.3, 1.48814}, {3.4, 
  1.48592}, {3.5, 1.48366}, {3.6, 1.48158}, {3.7, 1.47997}, {3.8, 
  1.47894}, {3.9, 1.47848}, {4., 1.47824}, {4.1, 1.47892}, {4.2, 
  1.47973}, {4.3, 1.4809}, {4.4, 1.48231}, {4.5, 1.48374}, {4.6, 
  1.48498}, {4.7, 1.48589}, {4.8, 1.4865}, {4.9, 1.4869}, {5., 
  1.48717}}

list2 = {{0.3, 1.70796}, {0.4, 1.69032}, {0.5, 1.66887}, {0.6, 1.65187}, {0.7,
   1.64455}, {0.8, 1.64575}, {0.9, 1.65089}, {1., 1.65581}}

list 3 = {4.1, 1.47892}, {4.2, 1.47973}, {4.3, 1.4809}, {4.4, 1.48231}, {4.5, 
  1.48374}, {4.6, 1.48498}, {4.7, 1.48589}, {4.8, 1.4865}, {4.9, 
  1.4869}, {5., 1.48717}}

and so on until 10 lists and all of them have different lengths with some having (x,y) sets as much as 30 (x1,y1),(x2,y2) … (x30,y30) like that,

i want to list them list this in single excel sheet with two different options whichever you can guide,

list 1 x,y values (next list after two columns separation) –list 2 x,y values– (next list after two columns separation) -**list 3 x,y values****

list 1 x,y values (no separation)- list 2 x,y values– (no separation)- list 3 x,y values

How to rename the columns when the SharePoint list is exported to Excel

I have made a code that exports a SharePoint List to Excel. The columns of the list are: “Date”, “Before”, “Between”, “After”, “Remark”.

Now in the code I would like to rename the column names “Before”, “Between” and “After” to
“00:00 – 07:59”, “08:00 – 15:59” and “16:00 – 23:59” when it gets exported to Excel.

Is that possible?

This is my code:

$SiteUrl = "xxxxxxxxxxxx"
$Global:selectProperties=@("Datum","Before","Between","After","Opmerkingen");
connect-pnponline -url $SiteUrl -Credential $cred

function ExportList($listName)
{ 
    try 
    {  
        $listItems=(Get-PnPListItem -List $listName -Fields $Global:selectProperties).FieldValues 
        $outputFilePath="c:Temp" + ($listName -replace "/","-") + ".xlsx"
        $hashTable=@()  
        foreach($listItem in $listItems) 
        { 
            $obj=New-Object PSObject             
            $listItem.GetEnumerator() | Where-Object { $_.Key -in $Global:selectProperties } | 
                ForEach-Object {
                    if( $_.Key -eq 'Datum' )
                    {
                        $obj | Add-Member Noteproperty $_.Key $_.Value.ToLocalTime().ToString("dd-MM-yyyy")
                    }
                    else
                    { 
                        $obj | Add-Member Noteproperty $_.Key $_.Value
                    }
                } 
            $hashTable+=$obj; 
            $obj=$null; 
        } 
 
        $hashtable | Export-XLSX $outputFilePath -Table -Autofit -Force
     } 
     catch (Exception) 
     { 
        $ErrorMessage = $_.Exception.Message        
        Write-Host "Error: $ErrorMessage" -ForegroundColor Red         
     } 
} 

Break data link of map charts in Excel

I have an Excel file with map charts to monitor wildfires from a national database. I create maps of e.g. number of fires per administrative region, for different years. I insert the map for say 2019 in PowerPoint for a presentation, and I go to file>info>Edit-links-to-files>break-link, so that the map doesn’t change when i chose 2020 in the original excel file.

That works for other types of charts, but the map-charts keep refreshing, even after breaking the link. I’ve also tried a paste special as a “Microsoft Office Graphic Object”, and breaking the link, but no sucess. I could paste as a picture of course, but I need other people in my organization to be able to change e.g. color scale, labels, etc.

I didn’t find any reference to that issue, someone is experiencing the same ? Any solution ?
Thanks a lot !

worksheet function – Excel formula multiple scenarios

Ok, I’m going to try to be as specific as possible, but I’m racking my brain over an excel formula I can’t figure out. What I’m trying to accomplish is to have one of three things populate based on a few variables in other cells.

Basically, what I want it to do is :

If L1 is > 0 and J1, N1, and P1 is 0, then populate the Cell with “silver”.

If J1, N1, or P1 is more than 0, populate with “low”.

Unless V1 is less than 50, in which case populate with “High”.

I have gotten close a few times, but ultimately I can’t figure it out and have been at it for several hours. Any help would be greatly appreciated!

microsoft excel – VBA – How to Delete Hidden Rows Efficiently

I run a list every day that has varying amount of rows, with different values/texts in them. On average, the list is 1000’s of rows long, but I filter it down to about 50. Is there a way to efficiently delete all those other unused rows using VBA? I need them gone for another part of my macro.

Triple if statement in Excel?

Just seeing if anybody can help me out. I am getting lost in my written statements:

The formula I am trying to write is in Column AV.

Column G is "Current #" Column AZ is "Optimized #"

Column BB is for Strictly for Removals and can have multiple #’s separated by a comma.

If # in column ba = then replace, if its in BB then remove, If device type = RET then Retain.

=IF(VLOOKUP((@(Device ‘#)),OPT_300Carlton((Replacing ‘#):(Device Type ‘#)),6,FALSE)="RET","Retain Use New#",IF(VLOOKUP((@(Device ‘#)),OPT_300Carlton((Replacing ‘#):(Device Type ‘#)),1,FALSE)=(@(Device ‘#)),"Replace Use New#","Remove Use New"))

THis is what I written, but its failing because of too many functions.

excel vba export rows to text with datadump

This script does everything I need but one thing: it only exports Col B, and I need it to export col B:H (using A as the filename, which this script does).

Sub DataDump()

Dim X
Dim lngRow As Long
Dim StrFolder As String

StrFolder = "C:temp"
X = Range((a1), Cells(Rows.Count, 2).End(xlUp))
For lngRow = 1 To UBound(X)
Open StrFolder & "" & X(lngRow, 1) & ".md" For Output As #1
Write #1, X(lngRow, 2)
Close #1
Next
End Sub

Purpose:
I need to export each row as an md file, where Col A becomes the Title and the content is Cols B:H. I can do it in google sheets, but as the source is in excel I’d prefer to use VBA. This is the first successful (and super fast) script I’ve found (I have 7,000 rows).

What I’ve tried
I’ve tried modifying the range, and I’ve increased all the “2”s to “5” thinking it was a column thing, but it isn’t; selecting 5 just means it uses that single column.

I rarely use excel, so I really don’t know my way around VBA, but I do have this google sheets script that does the trick (after I copy/paste 100 rows at a time from excel to gsheets), but man it is slow, and I can only do 100 rows at a time. Here’s the google sheets script if it helps:

    function saveRowsToMDfile() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange("A1:H100");
    var rows = range.getValues();
  var folder = DriveApp.getFoldersByName("Test").next();
  var files = folder.getFiles();
  while(files.hasNext()) files.next().setTrashed(true);
  rows.forEach(function(row) {
    var title = row(0); //set first element of array as title
    row.shift(); //remove first element of the array 
    var content = row.join("n");
    folder.createFile(title + ".md", content);
  }); 
}

ComboBox list Updataing VBA Excel

I am new on VBA and trying to make a data entry form with two Combo Boxes, The values for list of these boxes I am getting from Sheet (“ItemList”),

Private Sub UserForm_Initialize() ThisWorkbook.Sheets("ItemList").Select ComboBox1.RowSource = "Category" End Sub

By this code I am getting the List for ComboBox1 and for Combobox2 i am getting the values by below code:
`

Private Sub ComboBox1_Click()
    Dim x As Integer

    x = ComboBox1.ListIndex

    Select Case x

    Case Is = o

    ComboBox2.RowSource = "Appliance"

    Case Is = 1

    ComboBox2.RowSource = "Cab"

    Case Is = 2

    ComboBox2.RowSource = "Exterior_Doors"

    Case Is = 3

    ComboBox2.RowSource = "Finish_Electrical"

    Case Is = 4

    ComboBox2.RowSource = "Finish_Plumbing"

    Case Is = 5

    ComboBox2.RowSource = "Hardware"

    Case Is = 6

    ComboBox2.RowSource = "HVAC"

    Case Is = 7

    ComboBox2.RowSource = "Interior_Doors"

    Case Is = 8

    ComboBox2.RowSource = "Painting"

    Case Is = 9

    ComboBox2.RowSource = "Rough_Plumbing"

    Case Is = 10

    ComboBox2.RowSource = "Tile"

    Case Is = 11

    ComboBox2.RowSource = "Water_Heater"   
    
    End Select 

    End Sub`

The Code is working fine but problem is that when I am hiding this sheet it’s giving me error.

I have one more problem is that i am trying to copy data from three different sheets in three tables to our inventory mgr Below code working fine but when someone inserts any column between these three tables everything is mess up please let me how to avoid this issue.

 Sub copyCsvData() 
    Set rnp = ThisWorkbook.Sheets("TrafficData").Range("$A$8:$A$5000")
    rnp.Copy    
    ThisWorkbook.Sheets("Inventory Mgr").Activate    
    ThisWorkbook.Sheets("Inventory Mgr").Range("$H$3:$H$5000").Select    
    ActiveSheet.Range("H3").PasteSpecial
    ActiveSheet.Range("S3").PasteSpecial
    Application.CutCopyMode = False
    Sheets("SalesData").Range("$A$14:D1000").Copy    
    Sheets("Inventory Mgr").Activate
    Sheets("Inventory Mgr").Range("$B$3:$E$1000").Select
    ActiveSheet.Paste 
    ThisWorkbook.Sheets("ListingData").Range("$D$3:D3000").Copy    
    ThisWorkbook.Sheets("Inventory Mgr").Activate   
    ThisWorkbook.Sheets("Inventory Mgr").Range("$R$3:$R$3000").Select
    ActiveSheet.Paste
    Set rnt = ThisWorkbook.Sheets("TrafficData").Range("$B$8:$B$3000")
    rnt.Copy    
    ThisWorkbook.Sheets("Inventory Mgr").Activate    
    ThisWorkbook.Sheets("Inventory Mgr").Range("$T$3:$T$3000").Select    
    ActiveSheet.Paste
    Application.CutCopyMode = False    
    ThisWorkbook.Sheets("Inventory Mgr").Range("$B$3:$B$1000").Copy
    ThisWorkbook.Sheets("Inventory Mgr").Range("$W$4:$W$1000").PasteSpecial
     Application.CutCopyMode = False    
 End Sub

excel – Input Box avoid blank entries

the vba copies data from the “source” to the “final” tab based on a date entered by the user the source has been reformatted (columns removed and added etc) in an “export” tab prior being copied in to the “final” tab. the vba below works but, I want to tighten the process and avoid the user from simply clicking on ok or cancel as this results in all data from the source spreadsheet being copied

 Public Sub Copydata()

  Dim CopySheet As Worksheet
  Dim PasteSheet As Worksheet
  Dim FinalSheet As Worksheet
  Dim nextRow As Long
  Dim FinalRow As Long
  Dim lastRow As Long
  Dim thisRow As Long
  Dim myValue As Date
  Set ws = ThisWorkbook.Sheets.Add(After:= _
         ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
         
  ws.Name = "Export"
  ' Get the sheet references
  Set CopySheet = Sheets("Source")
  Set PasteSheet = Sheets("Export")
  Set FinalSheet = Sheets("Final")


  lastRow = CopySheet.Cells(CopySheet.Rows.Count, "B").End(xlUp).Row
  nextRow = PasteSheet.Cells(PasteSheet.Rows.Count, "A").End(xlUp).Row + 1

  myValue = InputBox("Enter start date to transfer", "Input Date")

 For thisRow = 1 To lastRow
     
 If CopySheet.Cells(thisRow, "B").Value >= myValue Then
 
    CopySheet.Cells(thisRow, "B").EntireRow.Copy Destination:=PasteSheet.Cells(nextRow, "A")
    
    
  nextRow = nextRow + 1
  End If
  Next thisRow""

I had thought about a loop until the date was entered something like:

    Do
    myValue = InputBox("Enter start date to transfer", "Input Date")

    If myValue = "" Then
    MsgBox "You must enter a date as dd/mm/yyyy", vbOKOnly, "Invalid Date"
    Else
    Exit Do
    End If
    Loop

But it just loops even if a date is entered and doesn’t carry on with the code or errors with a type mismatch.

Any guidance would be appreciated thank you

microsoft excel – How to find the value of Column A on the first instance of Column B > Column C

Given the below table

enter image description here

I have Column A as a unique key value, I am looking to return the first key value where B > C

In this case the first instance is A = 3 Where B = 4 and C = 5

Can someone help me understand the LOOKUP formula to return the value 3?