Доброе время дамы и господа . На листе1 в столбце B идут цифры по возрастанию, но иногда с пропусками (например 1, 2, 3, 5, т.е. пропущена цифра 4). На листе 3 в столбце A идут цифры по возрастанию и без пропусков. В случае если цифра на листе1 столбца B совпадает с цифрой на листе3 столбца A, то: нужно цифру листа3 столбца 7 скопировать на лист1 в столбец 8 (соответствующей строки). Например: Цифра на листе1 столбца B =15 2-ой строки Excel и она совпадает с цифрой 13-ой строки Excel на листе3 столбца A =15, то: "1,00" цифру листа3 столбца 7 скопировать на лист1 в столбец 8 (соответствующей строки листа1, т.е. 2-ой строки Excel). Опасаюсь за 54-ую строку листа3 - она содержит объединённые ячейки. Нужно ли в данном случае избавиться от объединённых ячеек? Спасибо. Хороших выходных. (у кого они есть(.
А можно в столбец B листа1(содержащий цифры) добавить через "/" цифры листа3 столбца 7 ? В итоге получится "15/1,00" на листе1 в строке 2 Excel.
Доброе время дамы и господа . На листе1 в столбце B идут цифры по возрастанию, но иногда с пропусками (например 1, 2, 3, 5, т.е. пропущена цифра 4). На листе 3 в столбце A идут цифры по возрастанию и без пропусков. В случае если цифра на листе1 столбца B совпадает с цифрой на листе3 столбца A, то: нужно цифру листа3 столбца 7 скопировать на лист1 в столбец 8 (соответствующей строки). Например: Цифра на листе1 столбца B =15 2-ой строки Excel и она совпадает с цифрой 13-ой строки Excel на листе3 столбца A =15, то: "1,00" цифру листа3 столбца 7 скопировать на лист1 в столбец 8 (соответствующей строки листа1, т.е. 2-ой строки Excel). Опасаюсь за 54-ую строку листа3 - она содержит объединённые ячейки. Нужно ли в данном случае избавиться от объединённых ячеек? Спасибо. Хороших выходных. (у кого они есть(.
А можно в столбец B листа1(содержащий цифры) добавить через "/" цифры листа3 столбца 7 ? В итоге получится "15/1,00" на листе1 в строке 2 Excel.Yar4i
формула классная и она работает, я пытался ее с макросом совместить, но она Н/Д начала выдавать, все листы переименовал по аналогии с примером - все условия благоприятные в общем создал.
*** Всё понял, формула привязана и к имени файла. А у меня беда: у меня куча файлов и в каждом необходим этот столбец, если листы я переименую в "лист1" или "1" (как хотел) то с переименованием файлов - беда. *** Всё - убрал из формулы имя файла))) заработало. *** [vba]
Код
Sub Макрос1() Range("I1").Select ActiveCell.FormulaR1C1 = _ "=RC[-7]&""/""&INDEX('3'!R1C7:R701C7,MATCH(RC[-7]&"""",'3'!R1C1:R701C1,))" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I555"), Type:=xlFillDefault Range("I1:I555").Select Columns("I:I").Select Range("I553").Activate Selection.Copy Columns("H:H").Select Range("H553").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub
[/vba] Сюда вставил - Н/Д опять пишет. Возможно из-за отличных от "--" данных листа 3. (не указал - хотел облегчить задачу первоначально). Не могу уменьшить размер файла для прикрепления. Почти все удалил и он стал не информативен. Ссылки полетели(
формула классная и она работает, я пытался ее с макросом совместить, но она Н/Д начала выдавать, все листы переименовал по аналогии с примером - все условия благоприятные в общем создал.
*** Всё понял, формула привязана и к имени файла. А у меня беда: у меня куча файлов и в каждом необходим этот столбец, если листы я переименую в "лист1" или "1" (как хотел) то с переименованием файлов - беда. *** Всё - убрал из формулы имя файла))) заработало. *** [vba]
Код
Sub Макрос1() Range("I1").Select ActiveCell.FormulaR1C1 = _ "=RC[-7]&""/""&INDEX('3'!R1C7:R701C7,MATCH(RC[-7]&"""",'3'!R1C1:R701C1,))" Range("I1").Select Selection.AutoFill Destination:=Range("I1:I555"), Type:=xlFillDefault Range("I1:I555").Select Columns("I:I").Select Range("I553").Activate Selection.Copy Columns("H:H").Select Range("H553").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub
[/vba] Сюда вставил - Н/Д опять пишет. Возможно из-за отличных от "--" данных листа 3. (не указал - хотел облегчить задачу первоначально). Не могу уменьшить размер файла для прикрепления. Почти все удалил и он стал не информативен. Ссылки полетели(Yar4i
Сообщение отредактировал Yar4i - Понедельник, 05.12.2016, 08:19
Спасибо. Все подходит. Прикреплю весь код. Может кому-нибудь понадобиться к восьмиграфной КС-2ой добавить стоимость материалов из сметы. [vba]
Код
Sub ИзКС2вГС() 'копировать видимый массив из КС2 на новый лист 'выделить предварительно массив!!! 'Отключение обновления экрана для ускорения работы макроса Application.ScreenUpdating = False Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy 'Sheets.Add After:=ActiveSheet 'первоначально 'Sheets.Add Before:=Sheets(1) 'After:=ActiveSheet 'апострофф 'ActiveSheet.Name = 111111111 'апострофф Sheets.Add(Before:=Sheets(1)).Name = "ГрандСмета" 'Пелена Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'красота в третьем и четвертом столбце Columns ("C : D").Select 'что-то изменяется... пробелы удалите Selection.ColumnWidth = 40 Cells.Select Selection.RowHeight = 40 Application.CutCopyMode = False With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With 'удалить пустые строки Wasilich PS = Range("B" & Rows.Count).End(xlUp).Row For i = PS To 1 Step -1 If Cells(i, 1) = "" Or Mid(Cells(i, 1), 1, 1) = "." Or Mid(Cells(i, 1), 1, 1) = Chr(133) Then Rows(i).Delete End If Next 'для всех C D формула tt МВТ, Manyasha For Each cell In Range("c1:d" & Cells(Rows.Count, "d").End(xlUp).Row) cell.Value = tt(cell.Value) 'вызов UDF от МВТ Next cell 'Минуса убрать с цены (минус из 7-ого столбца в 6-ой) _Boroda_ For i = 1 To Range("F" & Rows.Count).End(xlUp).Row If Cells(i, 7) < 0 Then Cells(i, 7) = -Cells(i, 7) Cells(i, 6) = -Abs(Cells(i, 6)) End If Next 'стоимость материалов из сметы 3 в ГрандСмета Range("I1").Select ActiveCell.FormulaR1C1 = _ "=RC[-7]&"" ""&VLOOKUP(RC[-7]&"""",'3'!R25C1:R500C13,7,0)" 'nilem '"=RC[-7]&"" ""&INDEX('3'!R1C7:R701C7,MATCH(RC[-7]&"""",'3'!R1C1:R701C1,))" 'Manyasha Range("I1").Select Selection.AutoFill Destination:=Range("I1:I105"), Type:=xlFillDefault Range("I1:I105").Select Columns("I:I").Select Range("I97").Activate Selection.Copy Columns("H:H").Select Range("H97").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'СохрБезЗапроса Апострофф ActiveWindow.SmallScroll Down:=-100 Range("A8").Select ActiveWindow.View = xlNormalView ActiveWindow.Zoom = 100 Workbooks.Application.DisplayAlerts = False Excel.ActiveWorkbook.Save Application.Quit 'Включение обновления экрана для ускорения работы макроса Application.ScreenUpdating = True End Sub
[/vba]
и функция tt: [vba]
Код
Function tt(Text As String) Dim obj As Object Text = WorksheetFunction.Trim(Text) With CreateObject("VBScript.Regexp") .Ignorecase = False .MultiLine = False .Pattern = "(\d{1,3}-\d{1,3}-\d{1,3}- ?\d{1,3}) ([А-яЁё]+( [а-яё])?)" Set obj = .Execute(Text) If obj.Count > 0 Then Text = obj(0).submatches(1) & " " & obj(0).submatches(0) End With tt = Text End Function
[/vba] В КС-2 выделяем массив от 1 номера до нижнего ПЗ, лист со сметой переименовываем в 3 и жмем макрос. в итоге я получаю данные на листе ГрандСмета, которые экспортируются в саму ГрандСмету. (в примечании "столбец B" номер по смете и стоимость материалов) - мне удобно, чего и вам желаю. В файл добавил макрос, т.к. с ошибками отображается код.
Спасибо. Все подходит. Прикреплю весь код. Может кому-нибудь понадобиться к восьмиграфной КС-2ой добавить стоимость материалов из сметы. [vba]
Код
Sub ИзКС2вГС() 'копировать видимый массив из КС2 на новый лист 'выделить предварительно массив!!! 'Отключение обновления экрана для ускорения работы макроса Application.ScreenUpdating = False Selection.SpecialCells(xlCellTypeVisible).Select Selection.Copy 'Sheets.Add After:=ActiveSheet 'первоначально 'Sheets.Add Before:=Sheets(1) 'After:=ActiveSheet 'апострофф 'ActiveSheet.Name = 111111111 'апострофф Sheets.Add(Before:=Sheets(1)).Name = "ГрандСмета" 'Пелена Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'красота в третьем и четвертом столбце Columns ("C : D").Select 'что-то изменяется... пробелы удалите Selection.ColumnWidth = 40 Cells.Select Selection.RowHeight = 40 Application.CutCopyMode = False With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With 'удалить пустые строки Wasilich PS = Range("B" & Rows.Count).End(xlUp).Row For i = PS To 1 Step -1 If Cells(i, 1) = "" Or Mid(Cells(i, 1), 1, 1) = "." Or Mid(Cells(i, 1), 1, 1) = Chr(133) Then Rows(i).Delete End If Next 'для всех C D формула tt МВТ, Manyasha For Each cell In Range("c1:d" & Cells(Rows.Count, "d").End(xlUp).Row) cell.Value = tt(cell.Value) 'вызов UDF от МВТ Next cell 'Минуса убрать с цены (минус из 7-ого столбца в 6-ой) _Boroda_ For i = 1 To Range("F" & Rows.Count).End(xlUp).Row If Cells(i, 7) < 0 Then Cells(i, 7) = -Cells(i, 7) Cells(i, 6) = -Abs(Cells(i, 6)) End If Next 'стоимость материалов из сметы 3 в ГрандСмета Range("I1").Select ActiveCell.FormulaR1C1 = _ "=RC[-7]&"" ""&VLOOKUP(RC[-7]&"""",'3'!R25C1:R500C13,7,0)" 'nilem '"=RC[-7]&"" ""&INDEX('3'!R1C7:R701C7,MATCH(RC[-7]&"""",'3'!R1C1:R701C1,))" 'Manyasha Range("I1").Select Selection.AutoFill Destination:=Range("I1:I105"), Type:=xlFillDefault Range("I1:I105").Select Columns("I:I").Select Range("I97").Activate Selection.Copy Columns("H:H").Select Range("H97").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'СохрБезЗапроса Апострофф ActiveWindow.SmallScroll Down:=-100 Range("A8").Select ActiveWindow.View = xlNormalView ActiveWindow.Zoom = 100 Workbooks.Application.DisplayAlerts = False Excel.ActiveWorkbook.Save Application.Quit 'Включение обновления экрана для ускорения работы макроса Application.ScreenUpdating = True End Sub
[/vba]
и функция tt: [vba]
Код
Function tt(Text As String) Dim obj As Object Text = WorksheetFunction.Trim(Text) With CreateObject("VBScript.Regexp") .Ignorecase = False .MultiLine = False .Pattern = "(\d{1,3}-\d{1,3}-\d{1,3}- ?\d{1,3}) ([А-яЁё]+( [а-яё])?)" Set obj = .Execute(Text) If obj.Count > 0 Then Text = obj(0).submatches(1) & " " & obj(0).submatches(0) End With tt = Text End Function
[/vba] В КС-2 выделяем массив от 1 номера до нижнего ПЗ, лист со сметой переименовываем в 3 и жмем макрос. в итоге я получаю данные на листе ГрандСмета, которые экспортируются в саму ГрандСмету. (в примечании "столбец B" номер по смете и стоимость материалов) - мне удобно, чего и вам желаю. В файл добавил макрос, т.к. с ошибками отображается код.Yar4i
В данном файле наглядно видно, что в случае с трехзначными исходными данными в столбце "нумерация" формула почему-то не выводит итог. Т.е. начиная с ячейки I13 листа "ГрандСмета" и ниже. *** Ой разобрался по 500ую же работает. Простите
В данном файле наглядно видно, что в случае с трехзначными исходными данными в столбце "нумерация" формула почему-то не выводит итог. Т.е. начиная с ячейки I13 листа "ГрандСмета" и ниже. *** Ой разобрался по 500ую же работает. ПроститеYar4i