Perguntas[excel](coding)

Estou trabalhando em um código VBA para obter dados de uma lista do SharePoint e atualizá-los diretamente do meu arquivo Excel.

Meu código está realmente funcionando, e aqui está:

Option Explicit


Dim HomeSh As Worksheet


Sub ImportList()

    Set HomeSh = Sheets("Feuil2")
    
    Dim SpSite As String
    SpSite = "https://mybestwebsiste"
    
    Dim Guid As String
    Guid = "{51748b98}"
    
    On Error Resume Next
    HomeSh.ListObjects(1).Unlist
    On Error GoTo 0
    
    HomeSh.Range("A1:X10000").Clear
    
    Dim SiteSrc(0 To 1) As Variant
    
    SiteSrc(0) = SpSite & "/_vti_bin"
    SiteSrc(1) = Guid
    
    HomeSh.ListObjects.Add xlSrcExternal, SiteSrc, True, xlYes, HomeSh.Range("A1")
    
    Set HomeSh = Nothing
    
End Sub


Sub UpdateSPList()

    Dim theList As Object
    
    Set HomeSh = Sheets("Feuil2")
    Set theList = HomeSh.ListObjects(1)
    
    theList.UpdateChanges xlListConflictDialog
    
    Set theList = Nothing
    
End Sub

Quando eu chequei para integrar em outro arquivo/macro, algo não funciona bem: O editor não espera a conexão ser atualizada e vai. O problema é: Eu preciso desses dados atualizados para ter uma boa execução do meu código.

Eu estava procurando na internet alguns exemplos de código para esperar a conexão ser atualizada, e não encontrei a solução certa. Aqui está o que eu tentei:

• 1 :

  Aplicação.CalcularAtéAsyncQueriesConcluir

-2 :

HomeSh.ListObjects.Add xlSrcExternal, SiteSrc, True, xlYes, HomeSh.Range("A1")
DoEvents

Deveriam ser essas linhas que me deram alguns problemas?

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Vi que uma expressão como essa deveria ajudar, mas não sei como integrá-la no meu código.

While http.readyState <> 4
DoEvents

Obrigado pela ajuda !

A seguir estão as funções vba IsPictureInCell e CopyCellPictureToClipboard. Eu apreciaria suas modificações sugeridas para essas duas funções para que elas funcionem corretamente com imagens bloqueadas em células.

' Checks if a picture exists in the specified cell
Function IsPictureInCell(Cell As Range) As Boolean
    Dim shp As Shape, shpRange As Range
    For Each shp In Cell.Parent.Shapes
        Set shpRange = Cell.Parent.Range(shp.TopLeftCell, shp.BottomRightCell)
        If Not Intersect(shpRange, Cell) Is Nothing Then
            IsPictureInCell = True
            Exit Function
        End If
    Next
End Function
' Copies pic to clipboard if the Cell contains a picture
Function CopyCellPictureToClipboard(Cell As Range) As Boolean
    On Error Resume Next
    Dim shp As Shape
    For Each shp In Cell.Parent.Shapes
        If Not Intersect(shp.TopLeftCell, Cell) Is Nothing Then
            shp.Copy
            CopyCellPictureToClipboard = True
            Exit Function
        End If
    Next
    CopyCellPictureToClipboard = False
End Function

Estou desenvolvendo um controle de expiração que deve enviar avisos para as pessoas que os documentos estão próximos do vencimento, existem documentos que devem ser cobrados 180 dias antes do vencimento e outros com apenas 30 dias, a ideia é que esse VBA ao invés de enviar 1200 documentos em vários e-mails, envie uma lista de documentos vencidos para as pessoas, por exemplo: para a analista Valéria devemos enviar a coleção de mais de 100 documentos que estão vencidos, mas ao invés de enviar 100 e-mails, criar uma lista e enviar em um único e-mail deixando sua gestão em cópia. Abaixo segue o código e a foto da planilha:

Fiz esse código, mas ele não atende minha real necessidade:

    Sub alerta_email()

Dim MeuOutlook As Outlook.Application
Dim CriarEmail As Outlook.MailItem

Range("E14").Activate

Do Until ActiveCell.Value = ""

If ActiveCell.Offset(0, 1).Value < Range ("F14").Value or ActiveCell.Offset(0, 1).Value < Range ("F15").Value Then

Set MeuOutlook = New Outlook.Application
Set CriarEmail = MeuOutlook.CreateItem(olMailItem)

With CriarEmail
     .BodyFormat = olFormatHTML
     .Display
     .HTMLBody = "Automatic alert" & "<br>" & "The document: " _
     & ActiveCell.Offset(0,2).Value & " Belonging to the group " _
     & ActiveCell.Offset(0, -3).Value & " It will expire in " _
     & ActiveCell.Offset(0, 1).Value & " days."
     .To = Range("N14").Value
     .CC = Range ("O14").Value
     .Subject = "Contract expiration alert: " & 
      ActiveCell.Offset(0, -2).Value
     .Send
End With

End If

ActiveCell.Offset(1, 0).Select

Loop

MsgBox ("Alert sent successfully")

End Sub

Quando executo a macro ela também está considerando os documentos que não estão vencidos. E a lista inteira ela está considerando enviar somente para Ana e seu gerente, em vez de considerar outro analista e outro gerente deste analista.

Caderno de exercícios ] 1 Caderno de exercícios 2 ] 2

Tentei encontrar a resposta antes de escrever minha pergunta, mas sem sucesso.

Preciso verificar se a célula, digamos C1, está vazia (sem valores) em todas as planilhas de algum arquivo Excel e quero ver uma caixa de mensagem com nomes de planilhas onde C1 não esteja vazia.

O motivo é que tenho um arquivo com muitas planilhas e agora preciso adicionar alguns links, mas nem todas as listas têm o mesmo "formato" e também há algumas listas das quais não preciso, então preciso verificar essas duas coisas primeiro.

Até agora eu tenho esse código, mas algo está errado, porque sempre vejo uma caixa de mensagem com o texto: A célula C1 está vazia em todas as planilhas , mesmo se eu tiver alguns valores em C1 (eu os adiciono em 3 planilhas, por causa do teste desse código).

Sub CheckCellC1()
    Dim ws As Worksheet
    Dim nonEmptyCell As String
    nonEmptyCell = ""
    
    ' Browse all sheets in the current workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if cell C1 is empty
        If ws.Range("C1").Value <> "" Then
            nonEmptyCell = nonEmptyCell & ws.Name
        End If
    Next ws
    
    ' Result
    If nonEmptyCell = "" Then
        MsgBox "Cell C1 is empty in all sheets."
    Else
        MsgBox "Cell C1 isn't empty in those sheets:" & nonEmptyCell
    End If
End Sub

O que estou perdendo?

Agradeço desde já sua ajuda.

Atualização: executei meu código a partir de um arquivo PERSONAL.xlsb e durante o "teste" abri apenas um arquivo Excel.

O único problema real com meu código era usar ThisWorkbookem vez de ActiveWorkbook. Adicionar & vbLfno final da linha nonEmptySheets = nonEmptySheets & ws.Name & vbLfajuda muito com a entrada da caixa de mensagem, está mais fácil de ler agora.

Sou iniciante em VBA e sempre cometo erros como esse (tenho um pouco de dificuldade para entender a diferença entre "lugares" onde o código está e como isso pode impactar o código em si, mas esse é outro assunto), então obrigado a todos por me ajudarem com isso.

Tenho uma tabela no Excel chamada TMarks.

Inclui uma coluna chamada AdjustedMark, que contém valores entre zero e 100.

Às vezes, os dados nesta tabela podem ser filtrados. Quero contar o número de linhas em que o AdjustedMarké maior que zero, ignorando linhas ocultas. Criei a seguinte fórmula:

=AGGREGATE(3, 7, TMarks[AdjustedMark]/(TMarks[AdjustedMark]<>0))

No entanto, esta fórmula está retornando um #Valueerro, e não entendo o porquê. Também tentei isto como alternativa:

=AGGREGATE(9, 5, (TMarks[AdjustedMark]>0)*1)

Mas isso também retorna#VALUE!

Todas as linhas na AdjustedMarkcoluna da tabela são numéricas.

Estou tentando ajudar minha liga de golfe a automatizar a manipulação de planilhas de programação semanal escrevendo algum código VBA. O processo envolve a gravação de macros para imitar o processo deles. Ao tentar classificar em uma seleção de coluna parcial, a classificação puxa a linha acima da seleção (uma linha de cabeçalho na linha 6) para a classificação. Tentei de tudo e li muitos artigos sobre isso, não faz sentido para mim e não consigo consertar. Alguém aí pode me ajudar? Veja as capturas de tela abaixo para ver o que está acontecendo e o que tentei consertar.

capturas de tela do problema

Sou um iniciante absoluto em VBA sem nenhuma experiência em codificação e estou tentando fazer um tipo de aplicativo de inventário. Fiquei preso e tentei muitos trechos de código encontrados na internet, mas sem sucesso.

Estou tentando comparar valores de estoque (em stock_sheet ) com o valor da célula inserido nas colunas distribution_sheet ** Tr ou En e , se o valor dist for maior, digo ao usuário para inserir valores menores ou iguais aos valores de estoque.

Por exemplo:

Se o usuário inserir um valor nas células F5, H5, J5,...,BN5 em distribution_sheet (ou seja, Linha 5), ​​o VBA deve verificar esse valor em relação a stock_sheet!F5 ao percorrer os nomes de publicação (E5 para LastRow)

Se o usuário inserir um valor nas células G5, I5, K5,...,BO5 em distribution_sheet (ou seja, Linha 5), ​​o VBA deve verificar esse valor em relação a stock_sheet!G5 ao percorrer os nomes de publicação (E5 para LastRow)

Captura de tela-1 -> Esta é uma planilha de estoque com valores de estoque **onde os números das linhas podem variar **

Captura de tela-2 -> O código a seguir funciona nesta planilha

Captura de tela-3 -> O código que tenho não funciona nesta planilha com opções de idioma (Tr, En)

Qualquer ajuda seria muito apreciada.

Atenciosamente.

Aqui está o código atual que tenho até agora

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
        Application.EnableEvents = False
        For Each cell In Target
            For i = 13 To 31
                If Not Application.Intersect(cell, Range("F" & (i) & ":BO" & (i))) Is Nothing Then
                    If Not IsNumeric(cell.Value) Or (cell.Value) > Worksheets("stock_sheet").Range("F" & (i)).Value Then
                        MsgBox Worksheets("stock_sheet").Range("E" & (i)).Value & vbCrLf & " please enter a number between 1 and " & Worksheets("stock_sheet").Range("F" & (i)).Value & _
                        " for this publication!", vbCritical, "ERROR"
                        cell.Value = vbNullString
                        cell.Select
                    End If
                End If
            Next i
        Next cell
        Application.EnableEvents = True
    End Sub

Estou construindo a partir de uma pasta de trabalho que alguém criou anos atrás, onde a Planilha 1 tem informações primárias do equipamento inseridas em caixas de texto ActiveX e botões de opção, com controles ActiveX correspondentes abaixo dela para equipamentos secundários para fazer cálculos. As Planilhas 2 e 3 copiam as informações primárias do equipamento para caixas de rótulo ActiveX para fazer cálculos para outros equipamentos.

O código foi configurado como abaixo para copiar as informações do equipamento primário para as planilhas 2 e 3.

Private Sub Worksheet_Activate()
If Right(ActiveSheet.Name, 1) <> "1" Then
   Sheet2.label1.Text = Sheet1.textbox1.Text
   Sheet2.label2.Text = Sheet1.textbox2.Text
   Sheet2.opt1.Value = Sheet1.opt1.Value
   Sheet2.opt2.Value = Sheet1.opt2.Value
End If
End Sub

Adicionei outra planilha para fazer cálculos para outro equipamento, usando VBA correspondente, no entanto, quando clico da Planilha 4 de volta para a Planilha 1 (com os botões de opção originais), os valores do botão de opção da Planilha 1 são sempre limpos. Isso NÃO acontece nas outras 2 planilhas existentes, apenas na minha nova. Reduzi para esta sub específica (na planilha 4) que está causando os problemas, mas não consigo descobrir como resolvê-lo.

Tentei as seguintes variações sem sucesso para impedir que os botões de opção na planilha 1 fossem limpos.

• alterando Private Sub para um botão de comando ( Command_Click() )
• substituindo a declaração If por With Me
• removendo a declaração If/With Me completamente

Não há nenhum outro código que limpe esses botões de opção específicos, além de ThisWorkbook para um sub Workbook_Open() (e excluí-lo não fez diferença). Alguém tem uma ideia do que pode estar causando isso, ou código VBA para evitar que isso aconteça?

Fiz uma pasta de trabalho de teste sem dados confidenciais, e o problema acontece depois de clicar em cada uma das planilhas (2-4) e depois clicar de volta para a planilha1 (portanto, um pouco diferente da pasta de trabalho original). Também não vejo nenhuma diferença nas propriedades de controle. Link do OneDrive: https://1drv.ms/x/c/e4891d1fec732162/EeyXfmzcETlGjNDy7_St01EBUbOdZOTDH9U3SbMpPSM3kQ?e=HXCO93

Gostaria de manter os botões de opção em cada planilha, pois será difícil atualizar o VBA nas planilhas seguintes (+ estética).

Isto está usando o Excel do Office 365 for Business.

Obrigado!

Preciso escrever um script VBA usando um loop ou alguns loops, que no final imprimirão os dados, por debug.print, parecendo algo assim:

lata, kg, verde, ervilha, 24,79

Aqui está a imagem de como ficou minha planilha

Sub ExtractTableData()
        Dim ws As Worksheet
        Set ws = ActiveSheet
        
        Dim row As Integer, col As Integer
        Dim lastRow As Integer, lastCol As Integer
        Dim category As String, unit As String
        Dim color As String, item As String, value As Variant
        
        
        lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
        lastCol = ws.Cells(2, Columns.Count).End(xlToLeft).Column
    
        For row = 3 To lastRow 
            category = ws.Cells(row, 1).Value  ' Category (can/bag/jar)
            unit = ws.Cells(row, 2).Value      ' Unit (kg/piece)
            
            For col = 3 To lastCol
    
                If ws.Cells(1, col).MergeCells Then
                    color = ws.Cells(1, col).MergeArea.Cells(1, 1).Value
                Else
                    color = ws.Cells(1, col).Value
                End If
                
                item = ws.Cells(2, col).Value
                
                value = ws.Cells(row, col).Value
                
                If Not IsEmpty(value) Then
                    Debug.Print category & ", " & unit & ", " & color & ", " & item & ", " & value
                End If
            Next col
        Next row
End Sub

Posso fazer versões mais simples deste exercício definindo um intervalo e fazendo um loop por ele, mas quando se trata de células mescladas, na verdade não tenho esperança. Por favor, não vote negativamente na minha pergunta novamente, estou realmente procurando ajuda :((

Tenho uma tabela do Excel (Tabela de Vendas) que usa uma função INDEX MATCH em algumas colunas. Há 2 colunas específicas (Produto e Número da Peça) que estão usando uma coluna de número de material para extrair outros valores de outras tabelas. A coluna de número de material é preenchida com números que são armazenados como "Geral" e cada célula tem um erro que diz "Número Armazenado como Texto". O "Número armazenado como texto" não é um problema para a coluna do produto, mas É um problema para a coluna do número da peça. Quando altero a coluna para "Número", a coluna do número da peça se corrige (então sei que não é um problema com a função).

Além disso, a tabela de referência de número de peça tem os números de material e NÃO mostra um erro nos valores de número de material. No entanto, na tabela de produtos, o número de material MOSTRA um erro.

Alguém pode explicar por que os números armazenados como texto afetariam uma coluna e não a outra? Para sua informação: a fonte de dados é importada para a tabela principal por meio do VBA e não consigo alterar o conteúdo do arquivo de entrada. Talvez eu precise alterar o texto da coluna por meio do VBA na importação, mas realmente quero entender por que é um problema em um ponto, mas não no outro.

Fórmula para o produto:

=IF([@Location]="Non-AT","Non AT",INDEX(Table_Material,MATCH([@[Material Number]],Table_Material[Material Number],0),MATCH($T$2,'Material No. to Product'!Print_Titles,0)))

Fórmula para número de peça:

=INDEX(Table_ATNo,MATCH([@[Material Number]],Table_ATNo[SAP Material Number],0),2)

Mesa principal