天道酬勤,学无止境

excel

Inserting data into the same row that a button is located in

I have created a macro which copies data from one worksheet to another. I want a generalized macro which copies data from the same row number as that of button, instead of B2 as mentioned below in the code. Currently this code is working fine; the button text is updated and MacroA has been assigned to it. I read about topleftcell, but am unable to implement it. Sub MacroA() ' ' MacroA Macro ' Range("I2:J2").Select Selection.Copy Range("B2").Select Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True Range("D2").Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas

2021-10-27 04:09:24    分类:问答    vba   button   excel

VBA Findnext Issue

This code works until input the coding between the ********** I'm trying to do two different searches at the same time. Can someone explain what I'm doing wrong? Thanks Public Sub Swap() With Sheet1.Range("A:A") Set LastCell = .Cells(.Cells.Count) End With Set FoundCell = Sheet1.Range("A:A").Find(what:=cusip, after:=LastCell) If Not FoundCell Is Nothing Then FirstAddr = FoundCell.Address End If Do Until FoundCell Is Nothing account = Sheet1.Cells(FoundCell.Row, 2) ''#************************************* Set FoundCell2 = Sheet2.Range("B:B").Find(what:=account) If Not FoundCell2 Is Nothing Then

2021-10-27 04:08:38    分类:问答    excel   vba

VBA 的字符串长度有限制吗?(Is there a limitation to the string length with VBA?)

问题 我有一个来自 HTML 源代码的大字符串(大约 1,000,000 个字符长)。 我正在使用 msinet.ocx 查看来自适当网站的文本。 我已经编写了一小段代码,以便找到出现在不同关键短语(“组件附件矩阵”)之前的关键短语(“pkid=”),但它无法正常工作。 这是我现在所拥有的: workbench = Cells(columnNumber, 1).Value myURL = "http://beams.us.yazaki.com/Beams/ViewDetails.aspx?topic=document&pkid=" _ & workbench Dim inet1 As Inet Dim mypage As String Set inet1 = New Inet With inet1 .Protocol = icHTTP .URL = myURL mypage = .OpenURL(.URL, icString) End With CAMnum = InStr(mypage, "Component Accessory Matrix") intStart = InStrRev(mypage, "pkid=", CAMnum) + 5 newnum = Mid(mypage, intStart, 6) Cells(columnNumber, 2).Value =

2021-10-27 04:04:30    分类:技术分享    string   vba   excel

ASP.Net - Export SQL query result to Excel

I am looking to have the result of a SQL query exported to Excel, formatted into a template. Basically the user clicks on the datarow, the query executes for that particular parameter and the the user is given the option to save to Excel file. How do i insert this data into an Excel template. Can I bypass saving the Excel file to a location so the user is only prompted, thus if cancelled file is not saved? Cheers Bill

2021-10-27 04:03:14    分类:问答    asp.net   sql   vb.net   excel

Save Excel 2003 worksheet into an Access 2003 table

I'm trying to write a macro to export a single row from a single worksheet from an Excel 2003 workbook to a new row in an Access 2003 table. I'm new to VBA, and everything I've found on the web refers to going the other way -- from Access to Excel. I want this to be an export, not a link, and I don't care about keeping them synced after the export.

2021-10-27 04:00:13    分类:问答    excel   ms-access   vba

VBA Excel looping through folder

I have a macro I'm trying to run on multiple workbooks within the same folder. I currently have the following, but when I run it (by using F5 in VBA for excel), nothing happens. The excel VBA window simply flickers, but none of the workbooks, even the first one, is affected by the macro. If it helps, sometimes F5 asks me to confirm that I'm running "Sheet1.DoAllFiles." I'm very beginner, so I'm sure it's something simple I'm missing - but any help in getting this program to loop would be appreciated. Thanks! The looping code I found: Sub DoAllFiles() Dim Filename, Pathname As String Dim WB As

2021-10-27 03:59:26    分类:问答    excel   vba   loops

How to have msgbox appear when cell value hasn't changed in 30secs or 1min etc

I am trying to make a msgbox appear when a cell value in range B4:B160 hasn't changed in 30secs or 1min etc. So what will appear is something like 'Have you scanned?' and will only appear if they haven't entered data in that cell in 30secs but will also close itself after 6mins or when they do enter data. It would be great if i can adjust these times in the code. My spreadsheet is used for barcode scanning and is currently already running a userform when they enter the wrong data. Can I add another userform which is timed? or can I only have one going at a time? Apologies a lot of questions

2021-10-27 03:57:40    分类:问答    excel   vba   userform   msgbox

获取从 2 列数据计算的最大值(无辅助列)(getting the maximum value calculated from 2 columns of data (without helper column))

问题 我有一些数据,存储在 A 列和 B 列中。对于每一行,我想得到 A 列和列 BIe 单元格(1,2)-单元格(1,1)中的值之间的差异,依此类推其余的行。 第二步是确定第 i 行的差异是否是前面第 n 行的最大值。 使用辅助列,我可以使用公式或这样的代码来完成。 for i = 1 to LR cells(i,3) = cells(i,2) - cells(i,1) next i for i = 1 to LR if cells(i,3) = .max(range(cells(i-19,3),cells(i,3))) then cells(i,4) = "MAX" end if next i 虽然我不想添加辅助列,但哪些 vba 代码可以做到这一点? 你能告诉我代码,因为我可能不熟悉我需要的语法吗? 回答1 这能满足您的需求吗? Sub e() Dim i As Long Dim m As Double For i = 1 To LR If Abs(Cells(i, 1) - Cells(i, 2)) > m Then m = Abs(Cells(i, 1) - Cells(i, 2)) Next i MsgBox m End Sub 循环遍历行并检查当前最大差异m是否超过当前行差异。 它被包裹在Abs以帮助处理任何负值 但是正如 JvdV 所指出的,这不是一回事 -

2021-10-27 03:52:51    分类:技术分享    excel   vba   function   helper

过滤并列出特定文件名(Filter and list specific file names)

问题 我通过编写以下代码生成了一个文件列表: files = [file for file in Path(main_directory).rglob('*filename*v*.xlsx')] 它检索文件列表: files[0] = .../2018/filename 2018 v 1.xlsx files[1] = .../2019/filename 2019 v 5.xlsx files[2] = .../2020/filename 2020 v 4.xlsx files[3] = .../2020/filename 2020 v 5.xlsx ... files[13] = .../2020/filename 2020 v 10.xlsx 我该怎么做才能让输出只给我每年最大的v ,所以我会有这样的输出? files[0] = .../2018/filename 2018 v 1.xlsx files[1] = .../2019/filename 2019 v 5.xlsx files[2] = .../2020/filename 2020 v 10.xlsx 我必须获得最大的v这并不意味着它具有最新的修改日期,因此我无法使用该功能。 我试过os.path和re但我无处可去。 回答1 假设相同年份的文件名在一起,你可以试试这个。 x=["2018/filename 2018

2021-10-27 03:44:50    分类:技术分享    python   excel

How to copy cell from one workbook to an other workbook with its set style?

I have been trying to copy a cell from one workbook to another with formatting only to fail. I have tried numerous suggestion but nothing works. I have tried: def copyCellStyle(new_cell,cell): new_cell.font = copy(cell.font) new_cell.border = copy(cell.border) new_cell.fill = copy(cell.fill) new_cell.number_format = copy(cell.number_format) new_cell.protection = copy(cell.protection) new_cell.alignment = copy(cell.alignment) then use it like: new_cell.value=old_cell.value copyCellStyle(new_cell,old_cell) It runs without errors but the job is not done. I get errors with the other methods in the

2021-10-27 03:41:39    分类:问答    python   python-3.x   excel   openpyxl